Calculated Fields in Pivot Tables

rhmkrmi

Active Member
Joined
Aug 17, 2012
Messages
308
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.


 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Aug 17, 2012
Messages
308
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You need a calculated item, not a calculated field, since you are trying to subtract items in one field, not separate fields.
 

rhmkrmi

Active Member
Joined
Aug 17, 2012
Messages
308

ADVERTISEMENT



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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What formula did you try, and did you select a cell in the same field in the pivot table first?
 

rhmkrmi

Active Member
Joined
Aug 17, 2012
Messages
308
Thanks for that tip.
I now get the error message "There are too many records to complete this operation".

Thanks!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,536
Messages
5,636,890
Members
416,947
Latest member
asher_nk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top