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 that is due to the 0.00 i have in some fields, but even then it is incorrect.

 

 

as you can see the average should be £125.32 but is shows as £136.44

 

The above should be £67.11 but it comes up as £46.81.

 

any one know a way to fix this?

 

Thank you!

admin
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    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
        MVP
        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.

Resources