Forum Discussion
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% |
Perhaps like this
with
Avr Clicks/Open:=DIVIDE( SUM( Table1[Clicks] ), SUM( Table1[Opens] ), 0 )On which Excel version / platform you are? It could be don with DAX measure if you are on Windows.
- microsoft 365 personal
