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!!