How to Avoid Averages of Averages in Pivot Table

%3CLINGO-SUB%20id%3D%22lingo-sub-1074902%22%20slang%3D%22en-US%22%3EHow%20to%20Avoid%20Averages%20of%20Averages%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1074902%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20creating%20a%20pivot%20table%20to%20compare%20different%20advertisement%20types%20for%20pay-per-click%20campaigns.%20The%20problem%20I%20am%20experiencing%20is%20that%20the%20%22sum%22%20or%20%22header%22%20value%20is%20calculating%20the%20average%20for%20the%20segment%20based%20on%20the%20average%20click-thru-rate%20of%20all%20the%20ads%20in%20the%20segment.%20(essentially%20an%20average%20of%20averages)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20you%20can%20see%20that%20there%20is%20a%200.35%25%20CTR%20for%20responsive%20display%20ads.%20This%20is%20an%20average%20of%20the%20values%20in%20the%20column%2C%20rather%20than%20the%20actual%20CTR%20for%20all%20the%20responsive%20display%20ads%20(therefore%2C%20incorrect).%20The%20answer%20would%20be%3A%20Clicks%2FImpressions%3D%20Average%20CTR%20(7767%2F3204008%3D0.24%25).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20change%20the%20column%20header%20to%20calculate%20this%20metric%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162627i3E32DCF2011BD081%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22averages.png%22%20title%3D%22averages.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1074902%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1075376%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Avoid%20Averages%20of%20Averages%20in%20Pivot%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1075376%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F494164%22%20target%3D%22_blank%22%3E%40hfoster%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20old%20way%20(and%20simplest%20on%20the%20face%20of%20it)%20is%20to%20add%20a%20calculated%20Field%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162655iAD932B9712A85806%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EMore%20Advanced%20Option%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20often%20need%20calculations%20in%20Pivot%20Tables%20then%20these%20days%20the%20alternative%20is%20to%20load%20your%20data%20to%20the%20Data%20Model%20when%20creating%20the%20Pivot%20Table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162656i188C5FB2DE9BA3FA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20then%20write%20a%20DAX%20formula%20(called%20a%20measure)%20by%20right%20clicking%20on%20the%20table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162657i72AD456F8DCB9F3E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_2.png%22%20title%3D%22clipboard_image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162658i260B437494322912%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_3.png%22%20title%3D%22clipboard_image_3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162659i21047647DD3EBE7A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_4.png%22%20title%3D%22clipboard_image_4.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EHope%20that%20helps%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EWyn%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EMVP%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EAustralia%20(UTC%20%2B%20%3CLI-EMOJI%20id%3D%22lia_smiling-face-with-sunglasses%22%20title%3D%22%3Asmiling_face_with_sunglasses%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%3CSPAN%20style%3D%22font-style%3A%20italic%3B%22%3EIf%20this%20answer%20was%20the%20best%20solution%20please%20click%20the%20button%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-size%3A%2011.0pt%3B%22%3E%3CSPAN%20style%3D%22font-style%3A%20italic%3B%20font-family%3A%20Calibri%3B%22%3EI%20also%20happily%20accept%20likes%20%3C%2FSPAN%3E%3CSPAN%20style%3D%22font-family%3A%20'Segoe%20UI%20Emoji'%3B%22%3E%3Agrinning_face%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorclipboard_image_6%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F162660iC3939B036C564B7C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_7.png%22%20title%3D%22clipboard_image_7.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorclipboard_image_5%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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?

 

averages.png

 

 

1 Reply

Hi @hfoster 

 

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

 

clipboard_image_0.png

 

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

clipboard_image_1.png

 

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

clipboard_image_2.png

 

clipboard_image_3.png

 

 

clipboard_image_4.png

 

Hope that helps

 

Wyn

MVP

Australia (UTC +

 

If this answer was the best solution please click the button

I also happily accept likes

 

clipboard_image_7.png