Pivot Table Help

[optimistpessimism]

Hello!

Instruction say I have to put the 'Total Price' (which is a calculated field) onto the 'Values' of the Pivot Table. The formula I used for the 'Total Price' is "=UnitPrice*Quantity". I manually Calculated the "Los Angeles - Whole Wheat" (293.16) as well as the "Total Whole Wheat on the West Column" (1256.4). I have noticed that the result is different when I manually calculated it. The pivot table multiplies the sum to the count of items in that field.

Pivot Table

Fields

Total Price Formula

Manual Calculation of Los Angeles - Whole Wheat

Manual Calculation of Total Whole Wheat on the West Column

Shouldn't the Answer be 'Sum' (manual calculation) and not 'Count*Sum' (which appears on the Pivot Table) ? Or is it correct?

Thank you very much!!

(I am not an Excel pro)

 

[Alex Blakenburg]

It is not doing what you think it is doing. It is not multiplying Count x Sum of Qty x Unit Price.
What it is doing is Sum of Qty x Sum of Unit Price ?
There is no way of getting it to use anything other than the "Sum of" the fields you are using in the calculation.

The only caculations you should be using in a Pivot Table are those that work when you apply them to the SUM of the line items.
It doesn't make sense to multiple by the Sum of Unit Price.
The Price * Qty calculation needs to happen at the Detail row / line item level ie in the data that feeds into the pivot table.

You don't have the option of using Min, max or average in the calculation in case you were thinking along those lines and in any case mathematically they would also give you incorrect results.

Further reading: How to Create Excel Pivot Table Calculated Field Examples

 

[optimistpessimism]

It is not doing what you think it is doing. It is not multiplying Count x Sum of Qty x Unit Price.
What it is doing is Sum of Qty x Sum of Unit Price ?
There is no way of getting it to use anything other than the "Sum of" the fields you are using in the calculation.

The only caculations you should be using in a Pivot Table are those that work when you apply them to the SUM of the line items.
It doesn't make sense to multiple by the Sum of Unit Price.
The Price * Qty calculation needs to happen at the Detail row / line item level ie in the data that feeds into the pivot table.

You don't have the option of using Min, max or average in the calculation in case you were thinking along those lines and in any case mathematically they would also give you incorrect results.

Further reading: How to Create Excel Pivot Table Calculated Field Examples

ohhh so you're saying that I should add another column and put the formula for the total price there? or that the pivot table is correct?

thank you for answering :>

 

[Alex Blakenburg]

Yes I am saying that should add another column "to the data table" and put the formula for the total price there and not try to use a calculated field in the Pivot Table to do it.