Jan 19 2021 12:59 PM
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.
Jan 19 2021 01:04 PM
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).
Jan 19 2021 01:22 PM
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.