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)

Hi @emercado777! We noticed that your inquiry was left unsolved for quite some time now.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

Hi

Thanks for letting me know.

As for the solutions, I opted for a long but simple solution.

For task 1:

A01 = CALCULATE(
[Total Respondents],
SurveyResults[A01] >= 4)

Where “A01” would need to be updated based on the column’s name.