Calculated field (Sum) in Pivot tables

Hi,

I have attached a sample tab of the issue I am having … I'm getting better with Pivot tables but can't quite work out the way around this.


I have a table (attached) that has red, blue and green cars each with their individual car colour total target value.

The pivot table correctly sums the "Total" values for blue, green and red cars and displays the correct individual target for each colour (I'm using "max" as the value field setting to get the common value [all the same for a car colour] rather than sum which would, wrongly, give a summed total of individual cars).

The bit I cant get to work is that I want the difference between the "sum of total" (pivot column C) and "max of target" (pivot column D) … at the moment it is summing the "Target" data (A2:A12) for each car:

Example for "Red Cars": (7922.90*2)-1200 rather than (7922.90)-1200

I understand why its totalling but cant find a way to force it to just use the single value (the value I found using "max")

Anyone got a simple/clever/complex but understandable to my lizard brain fix for this?


Thanks

Sorry just noticed another thing with the "max of target" column in the pivot table … is there a way to total the 3 maxes so that the bottom row (D21) is their total not the maximum value?

Thanks for the reply JeteMc … been away so just getting back to this.

I only recently got into Pivot tables so slowly finding my way!

I originally did a sum below the pivot but, as you said, it doesn't push it down it just overwrites

I will keep hacking at it - your response is most helpful in finding my way

Cheers

@jetemc

Actually cracked it with a bit of a bodge … I created a helper column with a unique identifier "IF(E10="","",(COUNTIF(E10:E23,E10)=1)+0)" which returned a 1 or a zero plus added a "difference" column … used the 1 or 0 to blank any duplicate calc's allowing me to only have one instance of each number and therefore I could use simple totals!

I always tended to avoid hidden helper columns but the more I look at Pivot tables the more it seems to be the way to go

very much appreciate the assist!