Double click on filtered value pivot table value will return wrong data

**macroqmm9** · 03-11-2013, 10:05 AM

Hello,
I have the following data on a sheet:

Report Claim
100001 100
100002 101
100007 106
100007 107
100010 110
100010 111
100010 112
100015 114
100015 115

Then I inserted a pivot table based on the range of the above data as it follows:

Row Labels Count of Claim issue
100001 1
100002 1
100007 2
100010 3
100015 2
Grand Total 9

Now I need to filter the values so that I can only see the reports that close more than one claim.
Then I go to Value Filters, Greater than, and finally I write 1, and the result is as follows:

Row Labels Count of Claim issue
100007 2
100010 3
100015 2
Grand Total 7

Which is exactly what I want. But if I double click the Grand Total Result (7), this is what I get:

Report Claim issue
100001 100
100002 101
100007 106
100007 107
100010 110
100010 111
100010 112
100015 114
100015 115

I am seeing the entire data when I only want to see the 7 records I filtered previously, i.e. records 100001 and 100002 should not be displayed.
What is wrong? Does anybody have any clues?
Thanks,
Jose

**JosephP** · 03-11-2013, 10:31 AM

unfortunately that just won't work with pivot tables-value filters don't affect the show detail functionality

**macroqmm9** · 03-11-2013, 10:41 AM

Hi JosephP
But this feature works perfectly for Label Filters. Why shouldn't it work for Value Filters as well.
If you filter by Label Filters to Greater than 100003 you will get this:

Row Labels Count of Claim
100007 2
100010 3
100015 2
Grand Total 7

Than if you double click Grand Total result (7) you will get the 7 required records, as follows:

Report Claim
100007 106
100007 107
100010 110
100010 111
100010 112
100015 115
100015 114

Isn't it odd that a feature may work in Label Filters and not work in Value Filters?
Is there a workaround to solve this issue?
Thanks
Jose

**JosephP** · 03-11-2013, 10:52 AM

I know label filters work but value filters don't. I'd call it a bug-who knows what ms would call it? ;-)

I don't know what your ultimate goal is to suggest a workaround-perhaps sql.

**macroqmm9** · 03-11-2013, 11:35 AM

As you may imagine, this is a fabricated example. In real life I work with thousands of records and 99,9% of the cases, are single. The reason I need to do this filtering is to isolate the records with more than one report (because there are some) to study the trend of epidemic defects. In real life I also have reports that close hundreds of claims (these are called epidemic) and these are the ones that I am interested in even though they represent 0,01% of the records.
Any help would be welcome.
Thanks,
Jose

**JosephP** · 03-11-2013, 11:38 AM

perhaps an autofilter with a countif but really I think a database would be preferable

**macroqmm9** · 03-11-2013, 11:52 AM

Yes, I can ultimately solve it with a countif, though I always prefer lean solutions. In my understanding this is a very poor one as all the ingredients for the recipe are there but the cake is always overcooked...