Excel – Grand total of calculated field of pivot table

microsoft excelmicrosoft-excel-2010

My pivot table summarizes the total number of items sold and calculates commissions, say:

ItemA 15 $150
ItemB  6  $30
ItemC  9  $45
Total 30 $300

I want to calculate commission to salespersons: For each item, if it reaches 10 pieces on that day, $10 will be given per piece. Otherwise, it doesn't reach 10 pieces and only $5 for each piece sold.

I used a calculated field:

=if(Count>=10,10,5)*Count

The commission for each item is calculated correctly. However, the grand total commission is wrongly calculated based on the grand total number of pieces (30 * $10), instead a simple total ($150 + $30 + $45 = $225).

How can I correct my calculated field?

(We use MS Excel 2010)

Best Answer

The Grand Total on a pivot table is fixed to using the same calculation as the Calculated Field. So there isn't a way to modify it.

A solution is to perform the commission calculation in the data table.

You can then use the pivot table to summarize it. This results in the Grand Total being correct.

Related Solutions

Excel – Pivot table : absolute value of grand total in Excel

This issue is identified by Microsoft... see http://support.microsoft.com/kb/211470/en-us.

But I think a way to achieve this is to use an calculated item in the pivot. For example, if you have a dataset like this :

id | value
--
1  | 300 
--
2  | -400
--
3  | -500
--
4  | 600
--

A calculated item with this formula : =SUM(ABS(id[1])+ABS(id[2])+ABS(id[3])+ABS(id[4])) will produce a result of 1800, instead of 0 with the pivot table grand total. Notice that you need to have an "id" column in your data set. In the calculated field, id[i], with iindicate the item number as appears in the pivot table. For your case you have to iterate the formula until 20.

If you have a lot of rows or if the number of rows can vary, you could look to a macro for build automatically the calculated item.

Excel – How to add custom columns to Pivot Table (similar to Grand Total)

What you need is not a calculated field, but a calculate item.

Your column field has 12 items, namely the 12 months of a year. What you want is an additional column called Avg. So we need to add Avg besides the 12 items:

Activate (click on) any cell with a label of the column field, say the cell with Jan, then PivotTable tools -> Options -> Fields, Items & Sets -> Calculated Item (it is dimmed if you do not activate a cell for field labels.) Then, name a new item Avg and set the formula =AVERAGE(Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)

Add other calculated items similarly.

Best Answer

Related Solutions

Excel – Pivot table : absolute value of grand total in Excel

Excel – How to add custom columns to Pivot Table (similar to Grand Total)

Related Question