Filter pivot table results / calculated field

Hello!

My pivot table has a simple calculated field: Charges - Payment. I need the pivot table to show me only "0". How do I filter on it?

thanks
LadkissonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You can experiment it by creating a calculated field in the data table and create a helper column to check if the calculated field contains 0 and then return a True else a False in it. Now you may add that helper column in the Report field of pivot table and apply filter to show True only.
The formula for the helper column would be something like this....

=IF(D2=0,TRUE,FALSE)

Open in new window

Where D2 is the cell in the Calculated Field Column and this formula can be placed in col. E to return True or False based on the values returned by the Calculated Field.

If this doesn't help, you may consider uploading a sample workbook.
0
tomfarrarCommented:
Try this....
Filter-Zero.xlsx
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
+1 Tom. :)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

LadkissonAuthor Commented:
Tom,

I see that you managed to insert a filter on the calculated field, but I don't know how you did it....
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
To achieve that, right click on any value under the Row labels and select Filters --> Value Filters and then select your Calculated Field from the drop down in the first box, select equal from the second box and in the third box type 0 and click on OK.

Hope this helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LadkissonAuthor Commented:
Yea, I figured that out already:) Thanks!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I could offer some help.

Also you accepted my reply as an accepted solution, instead you should have accepted Tom's solution as an accepted solution or you could split the points.
If you have done this unknowingly, you can use Request Attention feature to reopen the question and reassign the points.
0
tomfarrarCommented:
Thanks, sktneer, it was a joint effort.  - Tom
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.