Calculated items/fields in pivot table

[Michael Lam]

Dear all,
I am new to excel pivot table and I would like to ask you all a
question regarding the calculated items/fields in pivot table.
I am making a pivot table whose data source is shown below:

Team Salesperson Amount Sold Price each
Team Person Amount Sold Price each
Paper A 50 35
Paper D 60 25
Pen D 500 4
Desk C 20 400
Desk A 10 700
Pen B 700 3.5
Clip B 500 2.5
Clip C 600 2
Desk B 15 600



I am thinking if it is a way to add a field in the pivot table
which can show the total sale of each Team. For example, the total sale of
Paper will be 50*35+60*25 = 3250.
Thanks very much!

Michael Lam

 

[Debra Dalgleish]

I would calculate the total sale in each row of the data source, and add
that field to the pivot table's data area.

But, to calculate in the pivot table:

Select a cell in the pivot table
From the Pivot toolbar, choose PivotTable>Formulas>Calculated Field
Type a name for the field, e.g. TotalAmount
In the formula box, type a equal sign
In the field list, double-click on 'Amount Sold'
Type an asterisk ( * )
In the field list, double-click on 'Price Each'
Click OK

 

[Debra Dalgleish]

That's why I would calculate the line totals in the data source table,
and add the LineTotal field to the pivot table. A calculated field sums
the underlying fields, then calculates on the totals.

In your original example, you had the Teams with subheadings for Price,
so the calculated field for each Team/price would have been correct.
When you summarize by Team only, you'll get the incorrect totals.