**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.*