Pivot table calculated field using MAX of another field

[Tim Zych]

How do I get a calculated field to calculate using the MAX of another field?

Say I have the following:

SIZE COLOR Max of Qty Sum of Cost Sum of QtyXCost
large blue 8 5 40
green 2 4 8
red 10 6 60
large Total 10 15 300*
medium green 4 11 44
red 5 8 40
medium Total 5 19 171*

I want the calculated field QtyXCost to calculate using the MAX of Qty.

*At the total lines, I want 300 to be 150, 171 should be 95.

This is a simple example. In reality, I have more row fields and therefore
many more nested levels of grouped totals.

 

[Debra Dalgleish]

The subtotals and total for a calculated field will always be a sum.
Perhaps you could calculate the QtyXCost outside of the pivot table.

 

[Tim Zych]

I'm not seeing how that will work. Max must be used at multiple rowfields.
How would your approach work?

 

[Debra Dalgleish]

Using your sample pivot table, if the 8 is in cell C5, and 5 is in cell
D5, then in cell F5, enter the formula: =C5*D5

Copy down to the end of the pivot table, and it would multiply each
MaxUnits by SumQty.

 

[Tim Zych]

thanks for replying. I was hoping for a built in method.