Excel PivotTable Average Subtotals - incorrect results

Question

One recurring problem that I come across is the discrepancy in the Average calculation in the Subtotals within a PivotTable. What am I missing?&nbsp;Here’s my example:&nbsp;Thanks in advance!&nbsp;

sergeibaklan · Answer

JJolie&nbsp;
What you calculate manually is average of averages, and pivot table calculates in total average on all items.
Other words, in PivotTable you have something like (a1+a2+a3)/3 and (a5+a6)/2 for the items and&nbsp;(a1+a2+a3+a5+a6)/5 for grand total.
&nbsp;
What you calculate manually is ( (a1+a2+a3)/3 + (a5+a6)/2 )/2

sergeibaklan · Answer

JJolie&nbsp;
Again, correct or not correct depends on what exactly we would like to calculate. If average of all sublevel averages, not the average for entire level - when we need to add data to data model, write DAX measure which calculates above and use it in PivotTable.&nbsp;

sergeibaklan · Answer

JJolie&nbsp;, it's possible, but that not out of the box setting. Again, that could be the measure created in data model using DAX language and after that used in PivotTable.

jjolie · Answer

SergeiBaklanIs there any way to get the PivotTable options to choose the correct option?

jjolie · Answer

SergeiBaklanThe reason why I call it 'incorrect' is because when people look at the averages and do the quick math on these numbers, they'll assume the calculation of the subtotal/average calculation is incorrect. People won't necessarily understand that the subtotal is basing its result on a different source of numbers.&nbsp;So, for clarity, I was hoping it would be possible to average the averages above and show that. Is this not possible? Or is there no easy way to represent this the way I'm asking for it? i.e. show the average subtotals as the numbers on the right instead?

Forum Discussion

Excel PivotTable Average Subtotals - incorrect results

5 Replies

Resources