Pivot table average

Question

Hi everyone. I want to&nbsp;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.

sergeibaklan · Answer

cchildaa&nbsp;
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.&nbsp;
&nbsp;

cchildaa · Answer

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.

sergeibaklan · Answer

cchildaa&nbsp;
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

cchildaa · Answer

Thanks for your help. However, if I do not have power pivot in my excel version, what could I do? Many thanks.

sergeibaklan · Answer

cchildaa&nbsp;
I could be wrong, but based on you screenshot

you are on Excel desktop for Windows.
Power Pivot add-in is still available for download for Excel 2010&nbsp;Download Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010 from Official Microsoft Download Center, I guess for Excel 2013 it exists as well. Starting from Excel 2016 Power Pivot is built-in, the only it shall be activated in Options. In general only data model is required, measures could be created without Power Pivot.
However, if you are on Android, Mac or web - Power Pivot is not available.
Perhaps something could be done with VBA if to use cached PivotTable, but I'm not sure.
If nothing of above is available, perhaps to use some other tools/formulae in addition or instead of PivotTable.

Forum Discussion

Pivot table average