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 ...
Need Help
Show and Tell
  • 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.

bdymit's avatar
bdymit
Resolver II
9 years ago

My guess is that you are receiving what you asked for, yet are unclear regarding the idea of filter context.

 

When in a pivot table, the "Total" is calculated separately from the items above it. When the Power Pivot engine is calculating the value for "Total", it does not recognize the numbers it calculated for your individual campuses.

 

What the engine is doing for each of the campuses is taking your base data, filtering it to only the specific campus, and then calculating. When it calculates the total, it just includes all the campuses. It DOES NOT average the averages.

 

Look at these two examples (table on the left contains the Average GPAs for all the students within a given campus, as shown on the table on the right)

 

In this first example, the averages match. This is because the calculations are the same (in regards to the number of students and the cumulative sum of the GPAs. Thus, the "Average of Average GPAs" coincidentally is the same, because each campus has the same number of students.

 

In this next example, I just added two more students to Campus5, but halved the GPAs for students 19 and 20. Thus, the sum for all the GPAs is the same, but the denominator has changed. So the Average of the Students GPAs is different, while the "Average of the Average Student GPAs" stays the same.

 

If you want to force Power Pivot to calculate the Average of the Campus Average GPAs, you can force it to do so. Please post your measure and we can tweak it to take the Average of all the Campus Average GPAs for the "Totals" row.

 

Let me know if any of this is unclear--Thanks!

 

Ben

chartford's avatar
chartford
Regular Visitor
9 years ago

Hopefully this is sufficient as a sample.  This will look different from my original post as that was quite lengthy.

 

Average of Term GPAColumn Labels      
Row LabelsCampus1Campus2Campus3Campus4Campus5Campus6Grand Total
Course X2.603.403.403.203.602.003.03
Fall 20162.603.403.403.203.503.003.19
Fall 2017     0.000.00
Winter 2017    4.004.004.00
Grand Total2.603.403.403.203.602.003.03

 

ID1Term DescriptionCampusCourse NameTerm GPA
Student1Fall 2016Campus1Course X4
Student2Fall 2016Campus1Course X2
Student3Fall 2016Campus1Course X1
Student4Fall 2016Campus1Course X3
Student5Fall 2016Campus1Course X3
Student29Fall 2016Campus2Course X4
Student30Fall 2016Campus2Course X3
Student31Fall 2016Campus2Course X4
Student32Fall 2016Campus2Course X3
Student33Fall 2016Campus2Course X3
Student55Fall 2016Campus3Course X3
Student56Fall 2016Campus3Course X4
Student57Fall 2016Campus3Course X4
Student58Fall 2016Campus3Course X3
Student59Fall 2016Campus3Course X3
Student92Fall 2016Campus4Course X3
Student93Fall 2016Campus4Course X3
Student94Fall 2016Campus4Course X4
Student95Fall 2016Campus4Course X3
Student96Fall 2016Campus4Course X3
Student149Fall 2016Campus5Course X3
Student150Fall 2016Campus5Course X4
Student151Fall 2016Campus5Course X3
Student152Winter 2017Campus5Course X4
Student153Fall 2016Campus5Course X4
Student189Fall 2017Campus6Course X0
Student190Fall 2017Campus6Course X0
Student191Winter 2017Campus6Course X4
Student192Fall 2016Campus6Course X4
Student193Fall 2016Campus6Course X2
  • bdymit's avatar
    bdymit
    Resolver II
    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.