Pivot Table Subtotals - Calculated field subtotal incorrect

[reddy]

I have a pivot that has the following:
I have 2 calculated fields for the last 2 columns
The subtotal for the last column is calculating incorrectly:

Activity Agent Std/Hr Vol Time CalcProd/Hr Calc%toStd
Presort John 12 25 2 Hrs (25/2)=12.5
(12.5/12)=104%
Presort Jane 12 55 3 Hrs (55/3)=18.3
(18.3/12)=152%
Subtotal 12 80 5 (80/5)=16 XXX

Please advise as to how to make the subtotal for the "Calc%toStd" calculate
correctly.

 

[smartin]

I have a pivot that has the following:
I have 2 calculated fields for the last 2 columns
The subtotal for the last column is calculating incorrectly:

Activity Agent Std/Hr Vol Time CalcProd/Hr Calc%toStd
Presort John 12 25 2 Hrs (25/2)=12.5
(12.5/12)=104%
Presort Jane 12 55 3 Hrs (55/3)=18.3
(18.3/12)=152%
Subtotal 12 80 5 (80/5)=16 XXX

Please advise as to how to make the subtotal for the "Calc%toStd" calculate
correctly.

Hi reddy,

You can get into trouble if a calculated field uses aggregated
calculations.

Does your formula for Calc%toStd refer to Std/Hr, Vol, and Time (should
be good), or Std/Hr and CalcProd/Hr (bad)?

 

[reddy]

The formula for Calc%toStd refers to Std/Hr and CalcProd/Hr. I am guessing
this is not possible huh?

 

[smartin]

The formula for Calc%toStd refers to Std/Hr and CalcProd/Hr. I am guessing
this is not possible huh?

You are not necessarily stuck. As long as you have Std/Hr, Vol, and Time
available, you can create valid CalcProd/Hr and Calc%toStd:

CalcProd/Hr = Vol / Time {fine; you did this already}

Calc%toStd = [CalcProd/Hr] / [Std/Hr] {don't do this--based on aggregate}
= (Vol / Time) / [Std/Hr] {do this}

Just make sure you base your forumlae on /unaggregated data/ instead of
the aggregated field [CalcProd/Hr]