Pivot table average

Copper Contributor

Hi everyone. I want to Untitled.png

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.

5 Replies

@cchildaa 

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.

@cchildaa 

Let say we have such sample table, calculate both Average and Average of Average.

image.png

Creating PivotTable add data to data model

image.png

In Excel options keep disabled automatic grouping

image.png

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

image.png

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

image.png

and another one average of averages

image.png

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

@cchildaa 

I could be wrong, but based on you screenshot

image.png

you are on Excel desktop for Windows.

Power Pivot add-in is still available for download for Excel 2010 Download Microsoft® SQL Server® 2012 SP1 PowerPivot for Microsoft Excel® 2010 from Official Microsof..., 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.