Pivot table does not display average correctly

[Erdi88]

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!

[#2]

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

[#3]

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

[#4]

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

[#5]

@Sergei Baklan , 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,

[#6]

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

[#7]

Hi @Sergei Baklan,

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.

[#8]

@PEDRO_MULLER 

Thank you for the sample file.

PivotTable calculates correctly average of all records for the given month, and in row Grand Total average for all records for all months. That's not average of averages for each Team Member and for each Role.

If at the bottom of sample table to add the total as

=SUBTOTAL(1,[ABC] )

which is average of ABC ignoring filtered rows, when on filtered table result will be the same as in PivotTable

or

With DAX measure we may calculate average of averages for this or that grouping, not sure what is your goal.