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.


 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
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,524
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,524
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,524
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,128,068
Messages
5,628,458
Members
416,318
Latest member
Mano130898

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