Calculate average grade

[Anonymous]

Hi everyone,

I want to calculate the average grade for four columns (CES, Location, Enjoyable, NPS) after they are unpivot (new column names: Question and Grade). The measure should take the number of respondents (N=) into account. So, the measure should be able to calculate like this: (N= * Question)/N= (maybe it's possible to make a calculated column first in Power BI and then calculate the average, but I am not sure if this is possible). 

Secondly, the NPS should be unpivot because it's one of the four questions. But that score goes from -100 till 100, while the other three questions (CES, Location and Enjoyable) are grades (1 till 10). So, the NPS should be excluded in the measure and needs a measure on its own. 

I received the following measure, but this measure was counting rows instead of counting respondents:

Average grade =

VAR UniekeResponses =

    CALCULATETABLE (

        SUMMARIZE (

            'average grade',

            'average grade'[Location code],

            'average grade'[Survey],

            'average grade'[Question],

            'average grade'[Month],

            average grade[N=],

            "Respondent", MIN( 'average grade'[Grade])

        )

    )

VAR Responses =

    AVERAGEX(

        UniekeResponses,

        [Respondent]

    )

RETURN

    Responses

The link for the Excel-file is:

https://drive.google.com/file/d/1BwAt6xqgJIOpAiAUjQGcnXSqDwb8bNDi/view?usp=sharing

I calculated what the average should be like. Those outcomes can be found in the sheet 'grade calculated'

The link for the pbix-file is: 

https://drive.google.com/file/d/1jB9TfYCL94TRspyfMP6EwhNnC8zes6vS/view?usp=sharing

In the file, you can find a measure which shows the correct results. Unfortunately, it doesn't make a distinction between the months (the graph shows a flat line). Secondly, it only works when you add the specific location column (the column named in the measure) to the table. The locations belong to a few units. When you want to add a hierarchy (unit > location) to a matrix table, it returns the right values for the locations, but it only returns the same average for all units instead of the average of all locations who belong the one specific unit. 

I hope it's an understanding and coherent story. If not, please let me know. I hope someone could help me with this problem. 

[dax · Accepted solution]

Hi @Anonymous ， 

I am not sure your expected output, you could refer to my sample(page 1) for details. If this is not what you want, please correct me.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @dax ,

At my first glance, it seemed to work. I'll use your measures for my original file, and I will check the results. Due to tight schedule, I can't check it shortly. But when I know for sure that the results are correct, I will Accept it as the solution.