Learn Excel - Filter Pivot Table Where Calculated Item is Not Zero - Podcast 1922

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Sep 9, 2014.
Tracy from Texas asks about how to filter a pivot table to only show the rows where the calculated item is not zero. With a data set of invoices and payments, I will show you how to create a pivot table, add a calculated item for Balance, hide the grand total column, then a hack to filter the pivot table to show items with a non-zero balance.
maxresdefault.jpg


Transcript of the video:
MrExcel podcast is sponsored by eduPOW, opens the $5 courses.
Check out my Excel plan and Excel partners The Dirty Dozen at tinyurl.com/edupow5.
Learn Excel form MrExcel podcast episode 1922.
Filter Pivot Table Where Calculated Item is Not Zero.
I have a great question today sent in by Tracy from Texas.
Tracy has a data set and she is going to create a calculated item to figure out invoice minus payment and she wants to see just the invoices where the balance is not zero.
All right, so, this will be fun.
So, Insert, Pivot Table, I want to create it on an existing worksheet right out here to the right, click OK.
Down the left hand side I want the Invoice number across the top I want type and then I want amount.
So, now, I can see for each invoice what the invoice was and what the payment was.
Let's get rid of the grand total so, back here on the Design tab, Grand Totals, On for Columns Only, which keeps the grand total at the bottom and then on the Analyze tab, Fields, Items & Sets, a new calculated item.
oh, it's grayed out that's because I have to be here along this dimension.
Field, Items & Sets, it back beautiful.
Okay so, is we're gonna call it balance is equal to the invoice amount double-click minus the payment amount.
Click OK, All right, so now, we have invoice payment and balance and I need to filter just the invoices where the balance is non zero and unfortunately there is no way to do that.
Well, no legal way, but what we're gonna do is we're going to use a great trick here that I've used in the podcast a few different times.
I'm gonna come outside of the Pivot Table to the cell immediately to the right of the last set of row, headers across the top and from there I'm gonna do Data and then turn on the Filter.
If you're inside of a Pivot Table, see it's grayed out you're not allowed to use a Filter inside of a Pivot Table, but from right here they happen to filter the adjacent data sets of now balance and we will that we want not the zero values and bam and there's our answer.
All right, I wanna thank Tracy for sending that question in and I wanna thank you for stopping by.
See you next time for another netcast form MrExcel.
 

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top