Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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:

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. 

1 ACCEPTED SOLUTION
dax
Community Support
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.

View solution in original post

2 REPLIES 2
dax
Community Support
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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors