Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need to add a field to my pivot table that includes the difference between values in two columns
Solved by E. A. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 17/07/2018 - 09:42
Hello there, welcome to Got It Pro.
Excelchat Expert 17/07/2018 - 09:42
Hope you are having a good day.
Excelchat Expert 17/07/2018 - 09:42
Could you please upload the file you are having problems with? Please use the attachments button on the right of the chat tab.
User 17/07/2018 - 09:43
I actually can't upload the file. However, I can describe/ type out what I am looking at
Excelchat Expert 17/07/2018 - 09:43
Alright. Could you please tell me which version of Excel are you using?
User 17/07/2018 - 09:43
Excel 2016
Excelchat Expert 17/07/2018 - 09:43
Great.
Excelchat Expert 17/07/2018 - 09:44
From the description, it seems like what you need is to add a calculated field in the Pivot table.
Excelchat Expert 17/07/2018 - 09:45
I assume the differences of the columns that are concerned are also in the pivot table?
User 17/07/2018 - 09:45
Quest, what if the actual and Q1 forecast columns come from a "scenario" column
Excelchat Expert 17/07/2018 - 09:46
You would have to add them to the pivot table first.
Excelchat Expert 17/07/2018 - 09:46
To insert a calculated field, all field must be in the pivot.
Excelchat Expert 17/07/2018 - 09:48
Alright. I think this will do.
User 17/07/2018 - 09:49
Okay
Excelchat Expert 17/07/2018 - 09:49
Go to Analyze > Field, items and sets
Excelchat Expert 17/07/2018 - 09:50
Click on calculated field.
User 17/07/2018 - 09:50
Okay
Excelchat Expert 17/07/2018 - 09:50
A window will popup.
Excelchat Expert 17/07/2018 - 09:50
Do you see Actual and Q1 forecast there?
User 17/07/2018 - 09:50
Yes it includes the Fiscal Year field and the Scenario
Excelchat Expert 17/07/2018 - 09:51
Are the Q1 forecast and Actuals values for the Scenario column?
User 17/07/2018 - 09:52
Q1 forecast and Actual are filters within Scenario
Excelchat Expert 17/07/2018 - 09:52
Alright.
Excelchat Expert 17/07/2018 - 09:54
Analyze > Field, items and sets > Calculated items.
User 17/07/2018 - 09:54
Calculated Field?
Excelchat Expert 17/07/2018 - 09:54
*Item.
User 17/07/2018 - 09:55
It doesn't give me that option
Excelchat Expert 17/07/2018 - 09:55
You would have to click anywhere in the "Scenario" column
Excelchat Expert 17/07/2018 - 09:55
before you go to analyze
Excelchat Expert 17/07/2018 - 09:55
Does that work?
User 17/07/2018 - 09:55
No
Excelchat Expert 17/07/2018 - 09:56
The sample data that you have added here.
Excelchat Expert 17/07/2018 - 09:56
Click on 5
Excelchat Expert 17/07/2018 - 09:56
Then try analyze.
Excelchat Expert 17/07/2018 - 09:56
On Excel in your computer.
User 17/07/2018 - 09:57
If I double click it, it take me to data that the pivot table is created from.
Excelchat Expert 17/07/2018 - 09:57
Not double click, Just a single click.
User 17/07/2018 - 09:58
If I single click, it only lets me see Calculated field
Excelchat Expert 17/07/2018 - 09:58
Does that happen when you click on Scenario as well?
User 17/07/2018 - 09:58
Calculated item is whited out
Excelchat Expert 17/07/2018 - 09:59
Well, add Scenario as a row.
Excelchat Expert 17/07/2018 - 09:59
Then you'll have the option for calcualted items.
User 17/07/2018 - 09:59
The value is tied to one of the "Values" in the Pivot Table
Excelchat Expert 17/07/2018 - 10:00
Then you'll have to add a column that is not part of the pivot.
User 17/07/2018 - 10:00
I moved the item to rows
Excelchat Expert 17/07/2018 - 10:00
That is a fix, but it would change your pivot drastically.
User 17/07/2018 - 10:01
I just wondered that if I adjusted the filters for the pivot table would it mess with the difference column I would create?
Excelchat Expert 17/07/2018 - 10:01
So, if you only need the differences for presentation, adding it beside the pivot is the best option.
Excelchat Expert 17/07/2018 - 10:01
Nope it wouldn't
Excelchat Expert 17/07/2018 - 10:01
It would calculate the cell values, *Not related to pivot*
Excelchat Expert 17/07/2018 - 10:02
So changing the filters won't affect.
User 17/07/2018 - 10:02
how would you add a difference column then?
Excelchat Expert 17/07/2018 - 10:02
Add the formula =D3 - C3
Excelchat Expert 17/07/2018 - 10:02
On the column beside the rightmost column
Excelchat Expert 17/07/2018 - 10:02
And drag it down till the bottom of the pivot table.
User 17/07/2018 - 10:02
When I drag that down it shows the same value over
User 17/07/2018 - 10:03
=getpivotdata
Excelchat Expert 17/07/2018 - 10:04
Yes, that's another fix.
User 17/07/2018 - 10:04
Do I have to do each row?
User 17/07/2018 - 10:04
I can't drag the formula?
Excelchat Expert 17/07/2018 - 10:05
Yes, you can.
User 17/07/2018 - 10:06
Oh I had to manually type in the cell reference. I could not click on the cells in the pivotable
Excelchat Expert 17/07/2018 - 10:07
Yes, does that work now?
User 17/07/2018 - 10:07
Yes, thank you
Excelchat Expert 17/07/2018 - 10:07
You're welcome.
Excelchat Expert 17/07/2018 - 10:07
Thanks for using Got It Pro. Please come back again with your Excel related queries.
Excelchat Expert 17/07/2018 - 10:08
Have a good day and if your query is resolved kindly end the session.
User 17/07/2018 - 10:08
I feel like it was a lot of work for a simple task
Excelchat Expert 17/07/2018 - 10:08
No worries.
Excelchat Expert 17/07/2018 - 10:08
Kindly end the session if your query is resolved.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.