Pivot Table Grouping Problem on Value Filter

Question

I have a simple pivot table with one field for Column Labels (Rating), one field for Values (Rating Count), and one field for Row Labels (Best Practice).  I have set a Manual Filter on Rating ("Had Trouble") and a Value Filter on Rating Count where Rating Count is greater than or equal to 5 (i.e., where 5 or more people rated their experience with a Best Practice as having "Had Trouble").

Instead of displaying only the Best Practices that had a Rating Count of 5 or greater, the pivot table is displayingall Best Practices, regardless of their Rating Count.

Can someone tell me why the Value Filter is being ignored and how to correct the problem?  Thanks in advance for any assistance.

Bob

Answer 1

1.  By Manual Filter you mean the checkboxes?

2. The Value Filter operates against the total column only, so if you have one Column Label field of Ratings which has a breakout into two components say Good and Bad then the pivot table ignores to numbers under Good and Bad and looks only at the Grand Total field even if it is hidden. 

3. I would suggest either using a manual filter outside the pivot table.  Without a sample its hard to say how this might be done.

The best way to share a file, allow others to help, and maintain everyone's privacy, is to post it to one of the free file-sharing sites and post the link here.

These are a few candidates:

MediaFire: http://www.mediafire.com/

FileFactory: http://www.filefactory.com/

FileSavr: http://www.filesavr.com/

FileDropper: http://www.filedropper.com/

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 2

Yes I mean create a column adjacent to the pivot table, possibly on the left of it using formulas (the GetPivotData function tracks not the cell but the contents).  Then apply an auto filter to that column.  I agree with you that it is an extra step but at the present time that's all Microsoft has given us.

The filter column can have regular formulas like =D4 or

=GETPIVOTDATA("g",$E$3,"Amount","Jan")

If the GETPIVOTDATA function does not automatically appear when you type = and click a cell in the pivot table you can choose PivotTable Tools, Options, open the Options drop down and choose Generate GetPivotData.

This function record absolute, so to make it easy to copy you need to modify it.  For example in the above formula I want to copy it down for each month so if C4 contains the Month entries, I change it to

=GETPIVOTDATA("g",$E$3,"Amount",C4)

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 3

Hi Shane,

1. Yes.
2. I was not aware that the Value Filter operated against the Total column only.  That explains why the Value Filter was not being applied against my "Had Trouble" Rating.
3. When you say, "... [use] a manual filter outside the pivot table", are you referring to the "Filter" option on the Data ribbon?  If so, I can certainly do that.  However, it is an extra step I was hoping to avoid.  I would have preferred to have done everythingwithin my pivot table.

Thanks for all your help.  I really appreciate it.

Bob

Answer 4

Hi Shane,

Thanks for your suggestion.  However, I already had checked the Allow Multiple Filters checkbox.  Despite having done that, I'm still having the problem.

Bob

Answer 5

Hi,

With the pivot table selected choose PivotTable Tools, Options, Options, Totals & Filters tab and check Allow Multiple filters per field.

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire