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.
Hey,
this is odd, using the sampledata you provided both SSAS Tabular variants (Power Pivot and Power BI) returning the same value
Power Pivot hosted in Excel 2016 (64bit)
and Power BI (August release 64bit)
Excited to learn what caused this issue
Regards