How do I filter Pivot Table Values?

[flyboy54]

I have a standard pivot table with 4 items in the values area.
I am using the Sum of those values.
I want to filter out the zero's in one of those values.
I select the value from the Fields area and select Filter Values.
I set it for Not Equal 0
But the zero values still show up.
Suggestions?
thanks

[judgeh59]

if the field that has zero's in it has a filter dropdown you can use that to filter zero's

[dilipandey]

Hi Flyboy,

Can you upload your sample workbook ?


Regards,
DILIPandey
<click on below * if this helps>

[flyboy54]

DILIPandey,
The table I actually have is proprietary. I have replicated the issue with a sample data set and have attached that.
The filter on Total works fine if you have two of the columns such as area and task. But when the third area of zone is added, the filter on tital no longer works.
In the attached example I have zero (0) and eight(8) turned off, yet they show up in the table.
Thanks.

[VKS]

Hello Flyboy, Please check the attached file and let me know if this is what you want.
Best Regards/VKS

[flyboy54]

VKS,
Not quite. I am not sure how you got all the buttons inside the pivot.
I really want to filter out just the rows that have a total of zero and eight.
If you look at the file I uploaded, there is a filter in the "Choose fields.." area. that has 0 and 8 deselected but those total rows show in the pivot table.
But I see that your solution does seem to work.
How did you get the (-) signs in the Q1-Q4 and the filters on those columns?
Thanks

[Andy Pope]

You might want to reconsider you data layout.

[flyboy54]

Andy,
Unfortunately, the actual data I have is in the format of the sample I have uploaded, and much larger.
I can certainly write some VBA to convert the layout as you suggest.
Thanks.

[flyboy54]

Andy, as a follow up, the Total column is the sum of the quarters Q1, Q2, Q3, Q4.
What is happening is I add the Total to the filter pane, deselect the values I don't want.
But that has no effect on what shows up in the pivot table.
What I really need to do is filter out rows where the values of Q1 and Q2 and Q2 and Q4 all equal zero.

[VKS]

Hello Flyboy,
Let me try and explain what I did
1. Pivot table values don’t let you filter anything
2. Values are controlled by row labels row labels allow you to filter
3. In my first attempt I took all Q’s in row labels and count of totals in value
4. In row labels I applied the zero filters and got what we got
In V2 (keeping 1 and 2 above in mind)
1. I took out all 4 Qs from row labels to values
2. And took total from values to report filter
3. In report filter I applied filter to 0 and 8 and dragged one more total in values
Above steps gave me the attached output.
Hope this helps..
Best Regards/VKS

[oeldere]

With the macro below to re-arange your data.

After that you can use a pivot table.

See the attached file.

Please Login or Register  to view this content.

[flyboy54]

Well it seems to be getting even more interesting.
Putting the Total in both the Values and the Filter should be doing the trick. With "normal" data that works.

However, when I apply the filter to Total and select just 0 in my real data, one entry shows up, but there are hundreds of rows that idsplay 0.
I now suspect that the data in the Total (which is calculated) is in fact not exactly zero, but very very close to zero.
I am relying on the pivot table to do a comparison between base line data and comparison data. the baseline data is positive and the comparison data is negative. So if the absolute values are equal, then the total should be 0. But when I filter on just zero, only one of hundres of apparent zero values actually appear.
So I will close this thread and mark it as solved, then open a new thread to explore possible errors in the pivot table calculations of the Total values.

Thank you all.

[oeldere]

Just stay on this topic, then other members can see what already been answered.

[flyboy54]

Ok, I have found what I think is the real issue (and marked it as unsolved).
I have built a simple spreadsheet (attached) to show what I have found.
I want to filter on the Pivot table combined total results, not the actual values in the total column.
In this example I have two sets of data, Baseline and Comparison (see column I)
The data in the comparison is the negative of the data in the Baseline for all rows except fourth, one, east. (highlighted in the file in yellow for both baseline and comparison)
When I filter Total to be = 0, third, one, west and third, one, west appear, because in the original data, they have a 0 for the total value.
However, what I really want to filter on the the sumed total value in the pivot table, which should show me all the rows in the pivot table except fourth, one, east.
So how can I filter the results to show the total sums in the pivot table that are not zero?
thanks.

[flyboy54]

Alternatively, how do I filter the pivot table on the Sum of Total to shw the items that has changed, fourth, one, east with a value of 6.

[flyboy54]

I have solved this programatically with some simple VBA.
I have included it below.
It starts a the first value in the Sumed Total of my actual pivot table, "E9".
It then hides all non 0 rows until it hits an empty cell (at the bottom of the pivot table).
This can be made far more robust, or alternatively hide the zero rows.
I have also included an "unhide all" that could be called at the beginning to start with a "clean slate".
thanks

Please Login or Register  to view this content.

[VKS]

Hello Flyboy, Please check the attached file. I dont think i understood your post 14 very clearly but atttached is as per my understanding.
Best Regards/VKS

[flyboy54]

I have created a very simple example. In the attached file there is data in columns A, B and C.
To make a better Pivot table I have rearranged the data in columns F, G, and H.
I then built a pivot table. How can I filter out the zero values in the pivot table so just Monday and Friday show up?
Thanks.

[DaveDeV]

Hi FB,

Is this what you were looking for?

FlyboyQuery-sample-DdV.xlsx

Best Regards,

Dave

[DaveDeV]

Or this?

Flyboy-sample4-DdeV.xlsx

[flyboy54]

Yes, that is what I am looking for. I tried that on my large data set and it worked.
This is SOLVED!
Thanks

[DaveDeV]

Hi FB,

If you wouldn't mind, I'd appreciate a "Click-on-the-Star" - I'm working on my reputation...

BTW, I'd also suggest you convert your input data to an Excel table - that way, you can add data elements and the table will dynamically extend and a "pivot refresh" will incorporate the added data

Dave