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
Highlighted
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
Highlighted

@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]

 

Highlighted
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
Highlighted

@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

Highlighted

@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