Calculated Fields in Pivot Tables

[rhmkrmi]

Hello,



Can I calculate two values in the same data field in apivot table?

In my source data, in column A that is labelled “Sale”, Ihave 2 values “Int’l” and “Domestic”.

In column B that is labelled “Amount”, I have dollar values.

When I create a pivot table, I get only one field for ”Sale”so I cannot calculate the difference between Int’l and Domestic.

Thank you.

 

[steve the fish]

Just drag 'Sale' into the rows part of the field list and 'Amount' into the values part. If it says count of amount left click, value field settings and change to sum.

 

[rhmkrmi]

Thank you.
I had done this but I a trying to insert a "Calculated Field" which can calculate the difference between Int'l and Domestic where I have Sale as Column label and Amount as Values.

 

[RoryA]

You need a calculated item, not a calculated field, since you are trying to subtract items in one field, not separate fields.

 

[rhmkrmi]

Thanks.



I tried that but it gives me the message “PivotTableformulas can only refer to items in the same field as the calculated item”.

 

[RoryA]

What formula did you try, and did you select a cell in the same field in the pivot table first?

 

[rhmkrmi]

Thanks for that tip.
I now get the error message "There are too many records to complete this operation".

Thanks!

 

[RoryA]

You may have to do some calculations in the source data then, creating separate fields for International and Domestic. Or do you have Power Pivot available?