Forum Discussion

cchildaa's avatar
cchildaa
Copper Contributor
Jun 01, 2024

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.

  • 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. 

     

    • cchildaa's avatar
      cchildaa
      Copper Contributor
      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.

        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

Share

Resources