Merging filters in PivotTable

%3CLINGO-SUB%20id%3D%22lingo-sub-1807685%22%20slang%3D%22en-US%22%3EMerging%20filters%20in%20PivotTable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1807685%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20Excel%20file%20with%20PivotTable%20Fields.%20In%20one%20of%20the%20fields%20it%20has%2050%20filters%20that%20can%20be%20selected.%20I%20only%20want%205%20of%20these%20filters%20to%20work%20with.%20What%20I%20would%20like%20to%20do%20is%20select%20those%205%20filters%20and%20then%20merge%2Fcombine%20them%20into%201%20new%20filter.%20Does%20anyone%20know%20how%20I%20can%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1807685%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1811883%22%20slang%3D%22en-US%22%3ERe%3A%20Merging%20filters%20in%20PivotTable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1811883%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F842037%22%20target%3D%22_blank%22%3E%40S_YOffice%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20I%20understand%20you%20have%20a%20Column%20with%2050%20unique%20items%20in%20it%20and%20you'd%20like%20to%20group%20those%20into%20a%20single%20item%20that%20you%20can%20then%20use%20to%20filter%20a%20Pivot%20Table%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20that's%20the%20case%20then%20using%20a%20mapping%20table%20to%20group%20the%205%20items%20into%20the%201%20item%20and%20the%2045%20items%20into%20%22other%22%20could%20work%2C%20and%20then%20either%20use%20that%20as%20a%20Table%20with%20a%20Relationship%20in%20the%20model%20or%20use%20Power%20Query%20to%20merge%20the%20new%20table%20definitions%20onto%20the%20the%20side%20of%20the%20existing%20table%20(option%202%20better%20if%20your%2050%20item%20table%20is%20currently%20being%20used%20as%20a%20lookup%20table%20rather%20than%20the%20fact%20table)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWyn%3C%2FP%3E%0A%3CP%3E%5BMVP%5D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1811887%22%20slang%3D%22en-US%22%3ERe%3A%20Merging%20filters%20in%20PivotTable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1811887%22%20slang%3D%22en-US%22%3EIf%20you're%20not%20using%20Power%20Pivot%20then%20you%20could%20add%20an%20extra%20column%20to%20the%20source%20table%20of%20your%20Pivot%20and%20copy%20down%20an%20XLOOKUP%20or%20INDEX%20MATCH%20formula%20that%20references%20a%20table%20that%20classifies%20your%205%20items%20and%2045%20other%20items%3C%2FLINGO-BODY%3E
New Contributor

Hello

 

I have an Excel file with PivotTable Fields. In one of the fields it has 50 filters that can be selected. I only want 5 of these filters to work with. What I would like to do is select those 5 filters and then merge/combine them into 1 new filter. Does anyone know how I can do this?

 

Thanks in advance

4 Replies

@S_YOffice 

 

Do I understand you have a Column with 50 unique items in it and you'd like to group those into a single item that you can then use to filter a Pivot Table?

 

If you're using Power Pivot then:

If that's the case then using a mapping table to group the 5 items into the 1 item and the 45 items into "other" could work, and then either use that as a Table with a Relationship in the model or use Power Query to merge the new table definitions onto the the side of the existing table (option 2 better if your 50 item table is currently being used as a lookup table rather than the fact table)

 

Wyn

[MVP]

 

If you're not using Power Pivot then you could add an extra column to the source table of your Pivot and copy down an XLOOKUP or INDEX MATCH formula that references a table that classifies your 5 items and 45 other items

@Wyn Hopkins 

Thanks for your response. I already have a Pivot Table that was sent to me, that has pre-set filters. Under one of the filters is 50 categories, I want to combine 5 of those 50 into a single category. I'm unsure how to use a mapping table, any help would be great. Thanks

@S_YOffice 

I'm not quite clear on what you mean,  but you may be able to Ctrl select multiple items and group them and then use them as a filter

 

image.png