Change total in pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-2076503%22%20slang%3D%22en-US%22%3EChange%20total%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2076503%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20pivot%20table%20I%20have%20two%20koloms%2C%201%20has%20the%20field%20option%20%22MAX%22%20and%20the%20other%20%22SUM%22.%3C%2FP%3E%3CP%3EIn%20the%20row%20total%20I%20want%20to%20have%20my%20results%20both%20as%20%22SUM%22.%3C%2FP%3E%3CP%3EIs%20this%20possible........%3C%2FP%3E%3CP%3EAttached%20a%20small%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advanced.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2076503%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2076552%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20total%20in%20pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2076552%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F935187%22%20target%3D%22_blank%22%3E%40Dirk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUnfortunately%2C%20that%20is%20not%20possible.%20The%20subtotals%20and%20grand%20totals%20in%20a%20pivot%20table%20always%20use%20the%20same%20aggregate%20function%20as%20the%20value%20field%20they%20belong%20too.%3C%2FP%3E%0A%3CP%3EYou'd%20have%20to%20place%20a%20formula%20outside%20the%20pivot%20table%20(but%20that%20might%20be%20a%20problem%20if%20the%20pivot%20table%20expands).%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Visitor

Hi,

 

In my pivot table I have two koloms, 1 has the field option "MAX" and the other "SUM".

In the row total I want to have my results both as "SUM".

Is this possible........

Attached a small example.

 

Thanks in advanced.

2 Replies

@Dirk 

Unfortunately, that is not possible. The subtotals and grand totals in a pivot table always use the same aggregate function as the value field they belong too.

You'd have to place a formula outside the pivot table (but that might be a problem if the pivot table expands).

@Dirk 

Creating PivotTable add data to data model and create measure

Max Amount:=IF(HASONEVALUE(Range[Name]),
      MAX(Range[Amount boxes]),
      SUM([Amount boxes]))

The logic is if one name is filtered (i.e. row) we calculate max, if more than one (i.e. grand total) we sum all.

image.png