Excel - General Question on Pivot Table - Expert Solution

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

Need a calculated field in a pivot table that finds the difference between two "count" value fields
Solved by K. S. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 30/08/2018 - 12:39
Hi
User 30/08/2018 - 12:39
Hi
User 30/08/2018 - 12:39
The doc i need help on is too large to upload
Excelchat Expert 30/08/2018 - 12:39
do you have the sheet yout working on
Excelchat Expert 30/08/2018 - 12:40
oh
User 30/08/2018 - 12:40
but
Excelchat Expert 30/08/2018 - 12:40
and so is the pivot
Excelchat Expert 30/08/2018 - 12:40
right
Excelchat Expert 30/08/2018 - 12:41
can you send me a screen shot of the pivot?
Excelchat Expert 30/08/2018 - 12:41
and what you need
Excelchat Expert 30/08/2018 - 12:43
hello you there
User 30/08/2018 - 12:44
yes
[Uploaded an Excel file]
Excelchat Expert 30/08/2018 - 12:44
ok
User 30/08/2018 - 12:45
I need a calculated field of column C-D
Excelchat Expert 30/08/2018 - 12:47
I will provide you with the steps..
User 30/08/2018 - 12:48
i tried =sum(count(MN_Track_ID)-COUNT(Publisher_Owner_Names))
User 30/08/2018 - 12:48
amongst others, and no cigar
User 30/08/2018 - 12:49
mind you I'm using Excel
Excelchat Expert 30/08/2018 - 12:50
yes.. we are rebuilding the same on Excel.. and will share the details..
User 30/08/2018 - 12:50
ok great
User 30/08/2018 - 12:50
the pivot table is functional in the same manner without the linked data set?
Excelchat Expert 30/08/2018 - 12:51
i just coppied the table you pasted on google sheet.
Excelchat Expert 30/08/2018 - 12:52
Please refer sheet3
[Uploaded an Excel file]
User 30/08/2018 - 12:52
that was originally a pivot table, as you can see in the screen shot i attched
Excelchat Expert 30/08/2018 - 12:52
yes.. i have seen the screenshot.. and recreated based on the same..
Excelchat Expert 30/08/2018 - 12:52
once you have the excel open let me know
User 30/08/2018 - 12:53
opening now
Excelchat Expert 30/08/2018 - 12:54
These are the steps to be performed..
Excelchat Expert 30/08/2018 - 12:55
Step-1 : Click on the cell inside the Pivot table and goto analyze tab.
User 30/08/2018 - 12:56
[Uploaded an Excel file]
User 30/08/2018 - 12:56
got it
User 30/08/2018 - 12:56
in my version, using the same formula i get as shown on the above screen shot
Excelchat Expert 30/08/2018 - 12:57
Step-2 : In the Fields, Items & Sets.. select Calculated Field
Excelchat Expert 30/08/2018 - 12:57
refer the screenshot..
User 30/08/2018 - 12:57
the MN_Track_ID is a number such as 4567489 (or so) so i think its somehow using that actual number rather than the "count"
Excelchat Expert 30/08/2018 - 12:57
[Uploaded an Excel file]
User 30/08/2018 - 12:58
yes yes
User 30/08/2018 - 12:58
i get it
User 30/08/2018 - 12:58
i saw your formula
Excelchat Expert 30/08/2018 - 12:58
coool :)..
User 30/08/2018 - 12:58
='MN_Track_ID,'-Publisher_Owner_Names
User 30/08/2018 - 12:58
that does not work in my version linked to my data set
Excelchat Expert 30/08/2018 - 01:00
can you change the values in the pivot from sum to count?
User 30/08/2018 - 01:01
no not letting me change for the calculated field, its grayed out
User 30/08/2018 - 01:02
[Uploaded an Excel file]
User 30/08/2018 - 01:02
as seen in screen shot above
Excelchat Expert 30/08/2018 - 01:02
Its Mac?
User 30/08/2018 - 01:02
yep
Excelchat Expert 30/08/2018 - 01:03
hmm... ok.. then the other alrernate which i can think of is...
Excelchat Expert 30/08/2018 - 01:03
to have a new column added to your original dataset...
Excelchat Expert 30/08/2018 - 01:04
and there you can have the same calculation built as a formula into each cell..
Excelchat Expert 30/08/2018 - 01:04
then add the new column to pivot table..
User 30/08/2018 - 01:05
ok
User 30/08/2018 - 01:05
trying that now
Excelchat Expert 30/08/2018 - 01:05
ok,
User 30/08/2018 - 01:07
no thats not going to work
Excelchat Expert 30/08/2018 - 01:07
Why??
User 30/08/2018 - 01:08
the data in column A is an aggregation of various rows
Excelchat Expert 30/08/2018 - 01:08
else if you share the excel.. i can check and build the "Calculated Field" in Microsoft Excel
Excelchat Expert 30/08/2018 - 01:08
and share it back
User 30/08/2018 - 01:09
i can email it via wetransfer
Excelchat Expert 30/08/2018 - 01:09
can you upload it dropbox.. and share the link here?
User 30/08/2018 - 01:10
yeah doing now
User 30/08/2018 - 01:13
https://app.box.com/s/n4m7c2w81e326kwm2iw5awea180v59uf
Excelchat Expert 30/08/2018 - 01:13
thanks.. downloading..
User 30/08/2018 - 01:13
ok
Excelchat Expert 30/08/2018 - 01:13
124MB file... WOW... :)
User 30/08/2018 - 01:14
yessir!
User 30/08/2018 - 01:14
or mam :)
Excelchat Expert 30/08/2018 - 01:16
Dowloading done.. excel is taking time to open!!!
Excelchat Expert 30/08/2018 - 01:16
Classic Microsoft problem.. :)
User 30/08/2018 - 01:18
ah a rare victory for mac excel
User 30/08/2018 - 01:22
any luck?
Excelchat Expert 30/08/2018 - 01:23
yes.. we will need to take a diff field for calculated item,,
Excelchat Expert 30/08/2018 - 01:23
as excel is taking the underlying value and then doing a substraction.. to give the output..
User 30/08/2018 - 01:23
rightt
Excelchat Expert 30/08/2018 - 01:24
is there an equivalent non number cell which can be used for pivot?
Excelchat Expert 30/08/2018 - 01:24
Instead of "MN_Track_ID,".. what other column can be used?
User 30/08/2018 - 01:24
hmm
User 30/08/2018 - 01:25
the answer is yes
Excelchat Expert 30/08/2018 - 01:26
another way of adding additional column is..
User 30/08/2018 - 01:26
just not sure if which would be better than the other
User 30/08/2018 - 01:26
you could use Track_ISRC
User 30/08/2018 - 01:26
that is a alphanumerical code
Excelchat Expert 30/08/2018 - 01:26
let me try that..
User 30/08/2018 - 01:27
ok
Excelchat Expert 30/08/2018 - 01:32
since the dataset is huge.. its causing delay while changing anything!!!
Excelchat Expert 30/08/2018 - 01:33
please bear with me while i build a solution..
User 30/08/2018 - 01:36
absolutely

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc