# Pivot Table - Average of average

Occasional Contributor

# Pivot Table - Average of average

Hello,

I am creating a pivot table with averages, but I am getting the wrong numbers because the pivot table is making the average of an average.

Anyone knows if there's a way to solve this?

E.g.

I am creating a pivot table with two rows, Group and Email number.

It calculates each average for each Email number, and then for the Group 1 it returns the average of the average, which is 6.87%.

If I would create the average with the totals of Opens and Clicks, 58/976 , it would be 5,94%.

 Email Group Email number Sum Opens Sum Clicks Avg Clicks/Opens Group 1 976 58 6.87% 1 145 21 14.48% 2 55 2 3.64% 3 34 4 11.76% 4 207 5 2.42% 5 77 11 14.29% 6 48 4 8.33% 7 85 2 2.35% 8 92 1 1.09% 9 233 8 3.43%
2 Replies

# Re: Pivot Table - Average of average

On which Excel version / platform you are? It could be don with DAX measure if you are on Windows.

# Re: Pivot Table - Average of average

Perhaps like this

with

``Avr Clicks/Open:=DIVIDE( SUM( Table1[Clicks] ), SUM( Table1[Opens] ), 0 )``