cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Calculate average grade

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:

VAR UniekeResponses =

CALCULATETABLE (

SUMMARIZE (

)

)

VAR Responses =

AVERAGEX(

UniekeResponses,

[Respondent]

)

RETURN

Responses

The link for the Excel-file is:

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:

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.

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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
Not applicable

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.