Pivot Table with Calculated Fields

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,(250000+(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!!

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,(250000+(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

Thanks Debra,

Sorry can't really catch yr idea: The "copy of the Commission field to the
pivot table" means that I'll create 2 calculated fields like "COMMISSION_1"
and "COMMISSION_2" and then the 2nd one can be summed by column?

Florence

"Debra Dalgleish" wrote:

> 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,(250000+(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
>
>

After you've created the Commission field, it appears in your pivot
table field list.
From the field list drag Commission to the pivot table's data area,
where it will appear as Sum of Commission2
Right-click on the heading of that column, and choose Field Settings
Click the Options button
From the 'Show data as' dropdown, choose Running Total in
As the Base field, select your row field.
Click OK

Florence wrote:
> Thanks Debra,
>
> Sorry can't really catch yr idea: The "copy of the Commission field to the
> pivot table" means that I'll create 2 calculated fields like "COMMISSION_1"
> and "COMMISSION_2" and then the 2nd one can be summed by column?
>
> Florence
>
> "Debra Dalgleish" wrote:
>
>
>>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,(250000+(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
>>
>>
>


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