Forum Discussion

chartford's avatar
chartford
Regular Visitor
9 years ago
Solved

problem reconcile average total

Hi there, I'm very new to MS PowerPivot and this forum.  I've been researching how to use PowerPivot for, what will be a variety of reporting scenarios at our College.  In this test scenario I want ...
  • bdymit's avatar
    bdymit
    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.