Forum Discussion
problem reconcile average total
- 9 years ago
If you want the "Grand Total" to be the Average of the Term Average GPAs, then your issue is the one I posted about above. If it is not your issue, please be more specific with what you are looking for.
Let me know if I have recreated your dilemma below:
Here are the DAX formulas for the measures:
Average of Term GPA :=
AVERAGE ( Table1[Term GPA] )Average Term GPA - Fixed :=
AVERAGEX ( VALUES ( Table1[Term Description] ), [Average of Term GPA] )What AVERAGEX does is it iterates over a table, evaluates all the averages, and then averages those results.
VALUES returns a 1 column table of the distinct values in that column currently visible in the filter context.
So what we are doing is using VALUES to return this
Fall 2016
Fall 2017
Winter 2017
Then evaluating [Average of Term GPA] for each term
Fall 2016 3.19
Fall 2017 0.00
Winter 2017 4.00
And finally AVERAGEX averages all of the values we just evaluated.
Fall 2016 3.19
Fall 2017 0.00
Winter 2017 4.00
2.40
Let me know if this works!
Thanks.
Clearly I've got some additional research to do but you've certainly provided a glimpse into how this process works. Thanks for the assistance.
Happy to help.
The clearest, easiest outline to understand how the DAX engine is evaluating measures is this reference card.
https://powerpivotpro.com/2015/10/giving-back-steal-this-reference-card/
It explains concepts like filter context, which I mentioned.
Here are other helpful resources.
https://www.youtube.com/watch?v=a29YqDosRYY&t=241s
https://www.youtube.com/watch?v=29frZhkwCqQ
https://www.youtube.com/watch?v=z96HqHvoZNg
https://www.youtube.com/watch?v=klQAZLr5vxA&t=34s
https://www.youtube.com/watch?v=6ncHnWMEdic&t=45s
Best of luck!