Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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,(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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table with Calculated Fields
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table with Calculated Fields
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,(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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table with Calculated Fields
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
How do I set up filter for page fields in pivot table? | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
Calculated Field in Pivot Table | Excel Worksheet Functions | |||
pivot table question, sum fields? | Excel Worksheet Functions |