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.
