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.
