Summarize SUMX grand total as a grand average

[gxm204]

Hi, I have the following workbook to get weighted student grade averages, which I am doing with `SUMX()` in DAX (From what I understand, this is akin to `SUMPRODUCT()` in Power Pivot):

=SUMX(grades, [grade] * RELATED(weights[weight]))

It's working pretty much as I want, except for the Grand Total which is naturally summing up all of the weighted averages, rather than taking an average grade which makes more sense.

I don't see any easy way to adjust this, am I missing something? Thanks so much for your time and consideration.

 

[RoryA]

You could add a new measure using:

=if(ISCROSSFILTERED([student_id]),[Semester grade],AVERAGEX(VALUES(grades[student_id]),[Semester grade]))

then use that in the pivot instead of your existing measure.

 

[gxm204]

Very neat, thank you! That's a new function to me.