Forum Discussion
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 a summary view of Term GPA's for a given semester.
| Campus1 | 2.57 |
| Campus2 | 3.12 |
| Campus3 | 2.78 |
| Campus4 | 2.32 |
| Campus5 | 3.65 |
| Campus6 | 1.86 |
| Average received using PowerPivot | 2.57 |
| Average using the list of 6 entries | 2.716666667 |
I'm at a loss for come up with a recent why this is happening. If anyone could provide some insight, that would be extremely helpful. I've checked records counts between the raw data and the table in the PowerPivot and the record count tracks the same in both areas. I've also checked for things like data shifting from one column to the next but the import file is tab delimited and the Access table I import into, does emit any errors.
Please let me know if you need more information.
Thanks in Advance,
Chuck Hartford
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.
7 Replies
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
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
https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/
This article summarizes the concept very well.
credit to austinsense and the P3 Team
Hopefully this is sufficient as a sample. This will look different from my original post as that was quite lengthy.
Average of Term GPA Column Labels Row Labels Campus1 Campus2 Campus3 Campus4 Campus5 Campus6 Grand Total Course X 2.60 3.40 3.40 3.20 3.60 2.00 3.03 Fall 2016 2.60 3.40 3.40 3.20 3.50 3.00 3.19 Fall 2017 0.00 0.00 Winter 2017 4.00 4.00 4.00 Grand Total 2.60 3.40 3.40 3.20 3.60 2.00 3.03 ID1 Term Description Campus Course Name Term GPA Student1 Fall 2016 Campus1 Course X 4 Student2 Fall 2016 Campus1 Course X 2 Student3 Fall 2016 Campus1 Course X 1 Student4 Fall 2016 Campus1 Course X 3 Student5 Fall 2016 Campus1 Course X 3 Student29 Fall 2016 Campus2 Course X 4 Student30 Fall 2016 Campus2 Course X 3 Student31 Fall 2016 Campus2 Course X 4 Student32 Fall 2016 Campus2 Course X 3 Student33 Fall 2016 Campus2 Course X 3 Student55 Fall 2016 Campus3 Course X 3 Student56 Fall 2016 Campus3 Course X 4 Student57 Fall 2016 Campus3 Course X 4 Student58 Fall 2016 Campus3 Course X 3 Student59 Fall 2016 Campus3 Course X 3 Student92 Fall 2016 Campus4 Course X 3 Student93 Fall 2016 Campus4 Course X 3 Student94 Fall 2016 Campus4 Course X 4 Student95 Fall 2016 Campus4 Course X 3 Student96 Fall 2016 Campus4 Course X 3 Student149 Fall 2016 Campus5 Course X 3 Student150 Fall 2016 Campus5 Course X 4 Student151 Fall 2016 Campus5 Course X 3 Student152 Winter 2017 Campus5 Course X 4 Student153 Fall 2016 Campus5 Course X 4 Student189 Fall 2017 Campus6 Course X 0 Student190 Fall 2017 Campus6 Course X 0 Student191 Winter 2017 Campus6 Course X 4 Student192 Fall 2016 Campus6 Course X 4 Student193 Fall 2016 Campus6 Course X 2 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.
