The Calculated Field will show the calculation in its grand total,
instead of the sum.

You could add another copy of the Commission field to the pivot table,
and set it to show a Running Total. The last number in the new column
would show the total commission.

Florence wrote:
Dear all,

I have a pivot table contained 2 calculated fields:

"RANGE" =IF(REVENUE <=250000, 1, IF(REVENUE <=1000000, 2, 3))
"Commission"=IF(RANGE=1,REVENUE*0.03,IF(RANGE=2,(2 50000+(REVENUE-250000)*0.5)*0.03,(625000+(REVENUE-1000000)*0.25)*0.03))

Then the pivot table has been generated like this:

REVENUE RANGE Commission
0.00 1 0.00
17,500.00 1 525.00
79,625.00 1 2,388.75
226,944.90 1 6,808.35
10,500.00 1 315.00
21,250.00 1 637.50
6,750.00 1 202.50
30,030.00 1 900.90
35,100.00 1 1,053.00
261,978.60 2 7,679.68

The total revenue is "689,678.50" which is correct. However, the RANGE has
been also calculated as "2" so that the "total of Commission" has been
calculated as "14,095.18" (i.e. Applied "689,678.50" and "2" into the
formula).

The desired result should be the summation of the "Commission" column (0.00
+ 525.00 + 2,388.75 ... + 1,053.00 + 7,679.68) = 20510.68

How can I control the Pivot Table that the formula is only applied to data
lines but not applicable for sub-total lines?

Much appreciate for your help!!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html