Filtering the Value field in a Pivot Table

[katiapro93]

I give up! For the past few hours, I have been trying to figure out how do I filter the value field in a pivot table. I have a pivot table which gives me all the clients who purchased different items. I have placed the product in the column field and filtered it by the product that I want. Now I need to just show the ones who meet a certain number.
For Example: (Any client who buys 5 or more qualifies)

Client Product Qty
12 7oz 5
14 12oz 5
11 7oz 1

Report should show only the ones with Qty 5

Can anyone help me filter the Value field?

 

[Jerry Sullivan]

So i've noticed that it will filter some when i try to do greater than .1 but it doesn't filter everything less than .1 out. I noticed this when i did the extreme case of .5. there was only one data point with this, however the .55 point was ther but then there were a few points that were 11 other points still on the graph all less than .1. I'm not sure why this would happen.

The filter is applied to the total for the row not the individual points that represent the intersection of the row fields and the column fields.

For this subset of your data, if you were to apply a value filter to the DATE row field of >=0.55, then all the values for wk1 would be shown and all the values in wk10 will be hidden.

Excel 2013
	A	B	C	D	E	F	G	H
1	PRESS	43
2
3	TOTALWASTE %	PaperSize
4	Date>Brand	34.5	35.5	41.5	53.25	55.37	55.5	Grand Total
5	wk1	0.0491	0.4510		0.1184	0.0640	0.1058	0.7883
6	ACSNC35		0.4510					0.4510
7	RSGL45						0.1058	0.1058
8	RSSNC30	0.0491				0.0640		0.1131
9	RSSNC35				0.1184			0.1184
10	wk10	0.2087		0.0496	0.0706	0.1389	0.0584	0.5262
11	IRSCB30	0.2087		0.0496		0.0520		0.3103
12	RSGL45						0.0584	0.0584
13	RSSNC30					0.0869		0.0869
14	RSSNC35				0.0706			0.0706
Pivot

If you were to apply a value filter to the BRAND row fields of >=0.10, then the only row not hidden for wk10 would be the one on Row 11 of the screenshot. You can see that includes individual values that are smaller than 0.1 (eg PaperSize 41.5=0.0496); however the total of that row is 0.31.

If you reorganize your Pivot to have PaperSize in the row fields, you could apply a filter to the individual items.

 

[Jerry Sullivan]

One caution on the way you're summarizing the data in your example....

Be careful when summing percentages or ratios in your dataset. The problem is that the sum of all the record's ratios is not the same as the ratio of all the sums.

As a simple example, if you have 1 item with 20% waste and another with 40% waste, your total waste isn't 60%.
For that matter we can't even take the average of those numbers (30%), because we might have 20% waste on a large item (Paper Size=55.87) and 40% waste on a small item (Paper Size=34.5). In that case, the % of Total Waste will be somewhere between 20% and 30%.

When you need the ratio of the sums, a better approach would be to use PivotTable formulas through Calculated Fields and Calculated Items.

 

[tjgrassi]

Thanks for your help. I didnt know that it was looking at the subtotals for the categories. I had those turned off. I was able to see what I wanted if i put them all as row labels. I'm still not quite clear as to how excel sorts the data when I apply those filters but I think i'm just looking at too much data. Also I tried to get the data in to a form that it didnt have more than one point in the same caterogry of press date brand and size, but if there was i used the max function to show only the max percentage. Thanks again for your help.

 

[MrExcel]

This solution is a complete and utter hack!

Background: The normal old Filter (formerly AutoFilter) dropdowns would let you do this, but Microsoft greys that option out when you are inside of the pivot table.

Hack: There is one spot where Microsoft "forgot" to grey out the Filter icon!

Back in your original picture of the data, the last heading was in cell E2.

Go to the blank cell immediately to the right of that cell - in your case, cell F2.

From that cell, go to the Data tab and click the big Filter icon. Presto! You now have AutoFilter dropdowns on each heading. These operate differently than the pivot table filters.

Caution: As far as the Excel team knows, you can not filter a pivot table this way, so if you refresh the pivot table, they do NOT re-apply your hacked filters. You have to re-apply them.
Caution 2: Remember cell F2! The only way to turn off the filters is to go back that cell... the first cell to the right of your headings. If you are in any other cell, the Filter icon is greyed out.

I learned this trick from a guy named Dan in one of my Philadelphia seminars and I've exploited it in several of my videos. I think the first time was here:
MrExcel's Learn Excel #793 - Pivot Filter Hack - YouTube
Since I made that video, I realized you only have to select cell F2, not F2:A2.

Interesting side note: I did mention this to some Project Managers on the Excel team. It is clearly a bug, but it appears they have no interest in fixing the bug, so it should be available for our use far into the future.

Bill

 

[tjgrassi]

This is a cool trick! Unfortunately it doesnt work exactly like I'd want it to. It will filter a single column header and not all of them, so when it filters to show only rows greater than 10% it only looks at one paper size. Also the filter trick will work if you click on any cell out side of the pivot table.

 

[reflect47]

This is a cool trick! Unfortunately it doesnt work exactly like I'd want it to. It will filter a single column header and not all of them, so when it filters to show only rows greater than 10% it only looks at one paper size. Also the filter trick will work if you click on any cell out side of the pivot table.

1) High light all values you want to be effected by this
2) Using Conditional formatting highlight cells that are less than 1
3) Right click on your pivot table in row 2. This should be blank. Right under what what you are filtering by.
4) Drop down menu click mouse over filter. Then click on filter by selected cell color.

Poof they are gone. I have photo's of how to do this but no clue how to attach them.

 

[ExcelFroot]

Is this a feature only on Windows version of excel, as it simply cannot be found on mac version... I cannot choose which value field to filter, and basically do not get that dialog.

Most the steps on how to do this are in the first reply...



The last step is to fill in your criteria and value:

 

[bobby_smith]

Another solution is move to an adjacent cell next to pivot table and click the normal filter key or press Ctrl + shift +L and the columns will be filtered.
For example, if the last column of the pivot table is in column D, click a cell in column E and activate the filter button or Ctrl + shift +L.
This only works if you are in the adjacent cell and not the pivot table itself.

 

[Jerry Sullivan]

Hi Bobby, I appreciate you sharing that suggestion.

Be aware that filtering by cells outside the PivotTable will result in different values in Totals and SubTotal rows verses the method of using the PivotTable's Value filters.

When using the PivotTable's Value filters, the sub-totals will correspond to the visible data. Using the method you describe, the sub-totals will include data that are in the PivotTable report, but not displayed due to the filtered/ hidden rows.

 

[RoryA]

It's actually what Bill suggested in post 24.