• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table showing additional items in drop down

D

DashboardNovice

Member
I have a pivot table with a report filter with values of 8,9,10 and 7 (in that order). I have it filtered for 7 (for July).

In the Pivot table, I have a column called 'CLIN' with possible values of 0003, 0004, 0001 and 0002 showing in the pull down menu/autofilter.

However, in my raw data, if I filter for '7', the only value for the CLIN column is 0002. Why are the other 3 values showing in the autofilter? Since 0002 is the only possibility, shouldn't that be the only value in the autofilter?

Bonus question: Notice my first sentence about report filter values. Why is the 7 displayed last, instead of first where it would be in sequence? As far as I can tell, the formatting is consistent in the raw data.
 
The AutoFilter and PivotTable (PT) filters work slightly different from each other. With the AutoFilter, every time you click the drop down, XL recalculates what are all the options available to be displayed for that column, and lists them, up to 1000 unique entires. Order is sorted A-Z. Downside of this is that it requires more calculation overhead, but your info is "live".

With a PT, all the calculations were done during creating/last refresh. Each Field already has stored the list of possible values. Since this list was a straight read of that columns info, it doesn't get recalculated based on what the other fields are filtered on. Additionally, the order of list is same as order displayed. So, if you have the list sorted, it's sorted. If you have the list mixed, the filter is mixed. Advantage of the PT then is lower calculation overhead, but downside is that things aren't "live".
 
Re the report filters not being 'additive' like they are with tables, one way around this is to use Slicers, which grey out any items that aren't in the currently filtered subset. On the upside, they are easier to select things from. On the down side, they take up a bit more screen real-estate.

Re the bonus question...if you drag the offending field out of the pivot and then refresh the pivot and then redrag it back in, often it is resorted into the correct order.
 
You must log in or register to reply here.
Back
Top