Solved: problem reconcile average total

chartford · ‎08-17-2017

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

bdymit · ‎08-17-2017

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.

View solution in original post

bdymit · ‎08-17-2017

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 · ‎08-17-2017

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

bdymit · ‎08-17-2017

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