Pivot Table - Average of average

Occasional Contributor

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 GroupEmail numberSum OpensSum ClicksAvg Clicks/Opens
Group 1 976586.87%
 11452114.48%
 25523.64%
 334411.76%
 420752.42%
 5771114.29%
 64848.33%
 78522.35%
 89211.09%
 923383.43%
2 Replies

@EdgarEngieImpact 

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

@EdgarEngieImpact 

Perhaps like this

image.png

with

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