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.
