Grand total of calculated field of pivot table

Question

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)

Answer 1

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.

Answer 2

Finally, I come up with an answer myself. The point is that not a calculated field is needed, but a new field with two items, namely Qty and Comm for the total number of items and the commission.

Suppose the data is like this:

Items   Pcs
ItemA     2
ItemB     6
ItemC     4
ItemA     6
ItemA     7
ItemC     5

The pivot table will give something like

Row labels   Sum of Pcs
ItemA               15
ItemB                6
ItemC                9
Grand Total         30

Now, what I want is another item for the column field which calculates commission, but there isn't a field in the pivot table for the column field. So, I add a field in my data:

Items   Pcs  Field
ItemA     2   Qty
ItemB     6   Qty    
ItemC     4   Qty
ItemA     6   Qty
ItemA     7   Qty
ItemC     5   Qty

The new field is placed in the column field of the pivot table:

Sum of Pcs   Column labels
Row labels   Qty           Grand Total
ItemA                  15          15  
ItemB                   6           6
ItemC                   9           9
Grand Total            30          30

Hide the Grand Total column at Design tab, Layout group, Grand totals, On for Columns Only.

Now, activate the cell with the column label Qty. Then Options tab, Tools group, Formulas, Calculated Item.

In the Name box, name the new item Comm. In the Formula box, type =IF(Qty>=10,10,5)*Qty. Click Add.

Now the pivot table looks like this:

Sum of Pcs   Column labels
Row labels   Qty           Comm
ItemA                 15      150
ItemB                  6       30
ItemC                  9       45
Total                 30      225