Latest Enterprise DNA Initiatives

Measure that will Sum, then Group

Hi,

I find myself a bit stuck on perhaps two simple task.

I have a PBI that needs to do two things:

Task 1 -

  1. Make a measure that can be added to any column, that will do this…
  2. Sum all values >=4,
  3. then divide by total rows

Task 2 -

  1. Use the SupportFile as a support table
  2. Group the columns from the Results file by the criterial form the SupportTable file.
    For example, all columns from 1-3 in Results file will be grouped in “Overview” because they match with the “Sort” column in SupportTable file.

Any type of support will be greatly appreciated.

Sample.zip (68.6 KB)

@emercado777 Not sure if this is what you want?!
Sample.pbix (57.3 KB)

image

Table =
VAR TotalBySort =
    ADDCOLUMNS (
        DISTINCT ( Results[Column] ),
        "Sort",
            CALCULATE (
                VAR Total =
                    SUMX ( Results, Results[Value] )
                VAR RowCount =
                    COUNTROWS ( Results )
                RETURN
                    DIVIDE ( Total, RowCount ),
                Results[Value] >= 4
            )
    )
VAR GetDescription =
    ADDCOLUMNS (
        TotalBySort,
        "Description",
            VAR CurrentSort = Results[Column]
            VAR DescTable =
                CALCULATETABLE (
                    DISTINCT ( 'Description'[Category] ),
                    'Description'[Sort] = CurrentSort,
                    REMOVEFILTERS ( Results )
                )
            RETURN
                CONCATENATEX ( DescTable, 'Description'[Category], ", " )
    )
VAR GroupRows =
    GROUPBY (
        GetDescription,
        'Description'[Description],
        "Something", SUMX ( CURRENTGROUP (), [Sort] )
    )
RETURN
    GroupRows

Hi @AntrikshSharma ,

Thanks for having a look.

However, the total number of respondents should be 123, but in the file you sent over it says 1845.

I think your file is aggregating each response per category, instead of counting each row separately (i.e. counting the number of email addresses)