Forum Discussion

Erdi88's avatar
Erdi88
Copper Contributor
Jan 22, 2020

Pivot table does not display average correctly

Hello,   I hope someone can help me. I am trying to show an average nightly rate in my pivot table, but it does not display correctly. The difference ranges from £0.50 - £20.00. I get that some of ...
admin
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 22, 2020

Erdi88 

Let say you have two groups of records: A with a1,a2,a3 and B with b1,b2.

Average for A = (a1+a2+a3)/3

Average for B = (b1+b2)/2

 

Average for both A and B (that's what you have on top) is (a1+a2+a3+b1+b2)/5 and it's not equal to average of average ( (a1+a2+a3)/3 + (b1+b2)/2 )/2 which you see selecting inner results.

 

Thus correct or not depends on what you'd like to receive, average of average or average on entire group.

  • Erdi88's avatar
    Erdi88
    Copper Contributor
    Jan 22, 2020

    SergeiBaklan Thanks a million, that makes sense. how do i fix it that it will show the average of averages in the pivot table?

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 23, 2020

      Erdi88 , I guess that's with measures since we have to calculate such average of average. Let me illustrate on example.

      Creating PivotTable add data to data model, here add measure (with Power Pivot or through right click on PivotTable)

      Average AA:=VAR
	tbl =GROUPBY(
		GROUPBY(
		  Table1,
		  Table1[A],
		  Table1[B],
		  "Average B", AVERAGEX(CURRENTGROUP(),Table1[V])
		),
		Table1[A],
		"Average A", AVERAGEX(CURRENTGROUP(),[Average B]))
RETURN
	DIVIDE(SUMX(tbl,[Average A]),DISTINCTCOUNT(Table1[A]))

      That's first column in above Pivot Table which gives correct result.

      More simple measure

      Average A:=VAR
	tbl =GROUPBY(
		Table1,
		Table1[B],
		"Average B", AVERAGEX(CURRENTGROUP(),Table1[V])
	)
RETURN
	AVERAGEX(tbl,[Average B])

      but gives average on all V for Grand Total.

      Last column is  the "standard" average.

      • PEDRO_MULLER's avatar
        PEDRO_MULLER
        Copper Contributor
        Jul 28, 2023

        SergeiBaklan , Thank you for your insights. I have a situation that I believe is similar to the initial reported case, and I would greatly appreciate your input on it.

         

        I have created a PivotTable and noticed that groups 1 and 2 are correct (40% and 85%). However, I am unsure about the value (59%) displayed for the top grouping, which is US (row 6). 

         

         

        I was anticipating a 73%, which is meant to indicate individual rows. Additionally, I understand if the average of 63%, which would be calculated using the values of 40% and 85%.

         

        Can you help me understand where this value is coming from?

         

        Thank you,

         

Resources