Forum Discussion

hfoster's avatar
hfoster
Copper Contributor
Dec 19, 2019

How to Avoid Averages of Averages in Pivot Table

I'm creating a pivot table to compare different advertisement types for pay-per-click campaigns. The problem I am experiencing is that the "sum" or "header" value is calculating the average for the segment based on the average click-thru-rate of all the ads in the segment. (essentially an average of averages)

 

Below you can see that there is a 0.35% CTR for responsive display ads. This is an average of the values in the column, rather than the actual CTR for all the responsive display ads (therefore, incorrect). The answer would be: Clicks/Impressions= Average CTR (7767/3204008=0.24%).

 

How do I change the column header to calculate this metric?

 

 

 

excel

1 Reply

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Dec 20, 2019

    Hi hfoster 

     

    The old way (and simplest on the face of it) is to add a calculated Field

     

     

    More Advanced Option

    If you often need calculations in Pivot Tables then these days the alternative is to load your data to the Data Model when creating the Pivot Table

     

    And then write a DAX formula (called a measure) by right clicking on the table

     

     

     

     

    Hope that helps

     

    Wyn

    MVP

    Australia (UTC + 😎😎

     

    If this answer was the best solution please click the button

    I also happily accept likes 😀

     

     

Resources