pivot subtotal avg NOT EQUAL to pivot details avg

[koi]

hi all,


i have this problem and i cannot solve it, in my pivot there are 20 countries, period (jan) and value per each country


when i put the period & countries in rows, and values in values..


the subtotal of period showing me 81% avg, while when i tried to combine all value from each countries then avg the value..i get 82.4%


i think this is related with some 0.0 value in some of the countries..


but how can i solve it so when i avg all values from countries..it will showing the exact same avg as period subtotal?


attached the file

http://speedy.sh/HUgJK/pivot-average-value.xlsx


thanks all for the advice

 

[SirJB7]

Hi, koi!


If you select range C3:C1290 (all the lines with % in column C), you could see in the status bar that the average is 81.0%, the same as if you enter in any blank cell the formula =AVERAGE(C3:C1290).


If you select range G5:G29 (all country averages from pivot table) and you perform the same controls as before, you'd see that the value is now 82.6% (not 82.4% as you stated), and it's because the average of averages isn't equal to the general average.


A short example: 2 countries, 1st with 2 values, 100% and 90%, 2nd with 1 value, 5%: the general average is 65% (195/3), a PT would calculate 95% for 1st and 5% for 2nd, and the average over the PT will give 35% (70/2), which it actually differs -and it's correct- from the previous 65%.


Hope it helps.


Regards!

 

[koi]

hi SirJB,


i also notice that now, if i have 10 country with only single value, then pivot subtotal avg will be same with the detail avg,


however if i have 10 country but there is 1 country has double value, then the avg will be different since the division number change from 10 to 11 in pivot subtotal

 

[SirJB7]

Hi, koi!

Glad you solved it. Thanks for your feedback. And welcome back whenever needed or wanted.

Regards!