Forum Discussion
"Include new items in manual filter" greyed out if the field is in the filter area of a pivot table
I'm using Excel in Office 365. I have a pivot table where one of the fields in the source data can change over time (new products being added). In my pivot I need to exclude just one product, so I have product in the filter with that product deselected.
The problem is that when new product codes come into the data, because one product code is already deselected, all the newly added product codes come into the filter unselected. This makes the data in the pivot incomplete.
How can I ensure that the filter always includes new products?
If I moved the product field into the rows area of the pivot I could check the box to include new items, but that makes the pivot table huge and unwieldy - that doesn't work for my purposes.
Does anyone have any workaround?
Thanks
10 Replies
PaulMBACB Are you using Power Pivot? I was not able to duplicate this problem using either a regular table or Power Query as the pivot table source.
One possible workaround is to open the Power Pivot window (Manage Data Model) and add a calculated column called "Status" to the applicable table with the following formula:
=IF(Table1[Code]="ExcludedCode", "Inactive", "Active")Return to the workbook, add the new "Status" field to the Filters section of the pivot table and filter by "Active".
You could also simplify this even further by naming the calculated column "Active" with the following formula:
=Table1[Code]<>"ExcludedCode"...then filter by TRUE. However, this wouldn't make sense visually if you ever needed to clear the filter, as the report would then read "Active: All".
djclements Thanks for your reply. Honestly, I'm not quite certain what I'm using. It's not a basic pivot table where the source is a table of data somewhere in the spreadsheet. It's a mechanism provided by our helpful IT guy to allow us to get data from the data warehouse into Excel. It looks and acts just like a normal pivot table, but the data connection says the connection type is "OLAP query".
PaulMBACB Sorry, I'm not able to test Power View with an OLAP query on my system. You could pass my suggestion on to your IT guy and see if it's feasible, or wait for someone else to reply here.
Best regards.
