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
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,

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 29, 2023

      PEDRO_MULLER 

      If you have nothing else under US it shall be as you said

      first measure is Average and second one is average of averages:

      Average of ABC Groups :=
IF (
    ISFILTERED ( Table3[Item.] ),
    AVERAGE ( Table3[ABC] ),
    AVERAGEX ( VALUES ( Table3[Group] ), CALCULATE ( Table3[Average of ABC] ) )
)

      Without the file can't say why 59% appeared.

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

        Hi SergeiBaklan,

         

        I have included my spreadsheet for your convenience. Prior to sharing, I performed some cleanup. Then, Earlier today, I made a secondary Pivot Table to confirm that the numbers were consistent with the previous data, and they were.

          

        Please do not hesitate to ask me if you have any questions about this file. I appreciate your support and thank you again for it.

Resources