Forum Discussion
Pivot table average
Hi everyone. I want to
Hi everyone. I want to calculate average as 116,803,158.95, but pivot table displays as 3,899,938.53. Why is there a difference between them? How could I fix it? thanks.
Average shown below is the average of selected averages. PivotTable shows average of all values under the groups. Other words, if you expand all groups and take average of expanded values it shall be the same.
How to fix depends on what you'd like to have, average of all values or average of averages totals. If the latest, that could be done using data model if your Excel supports it. In brief, it shall be Excel desktop app on Windows.
- thanks for your explanation. However, I still quite don't understand how to fix it. I want to get average of the selected averages, Thanks.
Let say we have such sample table, calculate both Average and Average of Average.
Creating PivotTable add data to data model
In Excel options keep disabled automatic grouping
It's always better to have separate Date (aka Calendar) table, which could be created in Power Pivot or by other way, but we could skip that. When you add dates to PivotTable Rows, Excel automatically creates additional columns like Months
Now create in Power Pivot two measures
Value Average:=AVERAGE ( Table1[Value] )which actually repeats implicit measure when we aggregate values, and
AoA:=IF ( HASONEVALUE ( Table1[Date (Month)] ), [Value Average], AVERAGEX ( VALUES ( Table1[Date (Month)] ), [Value Average] ) )This one works as Average within the month (i.e. it has one value), and averages first measure iterating all months for the totals.
First measure returns average of all values for all months
and another one average of averages
