Pivot Table - Summing Averages in Grand Total

%3CLINGO-SUB%20id%3D%22lingo-sub-3520991%22%20slang%3D%22en-US%22%3EPivot%20Table%20-%20Summing%20Averages%20in%20Grand%20Total%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3520991%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20of%20values%20in%20a%20pivot%20table%20summarized%20as%20averages.%20I%20want%20the%20grand%20total%20for%20this%20column%20to%20add%20the%20averages%20as%20a%20sum.%20I%20know%20I%20can%20manually%20add%20the%20values%20with%20a%20simple%20formula.%20But%20is%20there%20a%20way%20to%20adjust%20the%20field%20settings%20to%20do%20the%20aforementioned%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3520991%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3521207%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20-%20Summing%20Averages%20in%20Grand%20Total%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3521207%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1266047%22%20target%3D%22_blank%22%3E%40Bjsmith2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20as%20far%20as%20I%20know.%3C%2FP%3E%0A%3CP%3EKeep%20in%20mind%20that%20a%20sum%20of%20averages%20is%20meaningless.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3522449%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20-%20Summing%20Averages%20in%20Grand%20Total%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3522449%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1266047%22%20target%3D%22_blank%22%3E%40Bjsmith2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20add%20data%20to%20data%20model%20you%20may%20play%20with%20DAX%20measures.%20For%20such%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20538px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20538px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F381503i7C5793F9038E1A26%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3Eaverage%20with%20sum%3A%3DSUMX%20(%0A%20%20%20%20SUMMARIZE%20(%0A%20%20%20%20%20%20%20%20Table1%2C%0A%20%20%20%20%20%20%20%20Table1%5BA%5D%2C%0A%20%20%20%20%20%20%20%20%22averages%22%2C%20AVERAGE%20(%20Table1%5BV%5D%20)%0A%20%20%20%20)%2C%0A%20%20%20%20%5Baverages%5D%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3Eaverages%3A%3DAVERAGE(%20Table1%5BV%5D%20)%0Aaverage%20with%20sum%202%20%3A%3D%0ASUMX%20(%0A%20%20%20%20VALUES%20(%20Table1%5BA%5D%20)%2C%0A%20%20%20%20%5Baverages%5D%0A)%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Visitor

I have a column of values in a pivot table summarized as averages. I want the grand total for this column to add the averages as a sum. I know I can manually add the values with a simple formula. But is there a way to adjust the field settings to do the aforementioned?

2 Replies

@Bjsmith2 

Not as far as I know.

Keep in mind that a sum of averages is meaningless.

@Bjsmith2 

If add data to data model you may play with DAX measures. For such model

image.png

it could be

average with sum:=SUMX (
    SUMMARIZE (
        Table1,
        Table1[A],
        "averages", AVERAGE ( Table1[V] )
    ),
    [averages]
)

or

averages:=AVERAGE( Table1[V] )
average with sum 2 :=
SUMX (
    VALUES ( Table1[A] ),
    [averages]
)