Filtering/Slicing a pivot table using multiple boolean values

[himitsu]

Hello, everyone. Hopefully this isn't a duplicate thread. I've done a fair amount of searching for the last few days and have been unable to find an answer to my problem. I have a fair amount of sql programming background, but I'm admittedly new to excel and VBA.

I'm trying to create a dashboard style report for some management personnel with very little tech knowledge. (A self explanatory and user-friendly interface is a hard requirement) They're used to working with slicers, and that style of interface would be my preference.

Here's where the problem comes in. I have a data set with multiple Boolean values stored in separate columns. Those columns indicate traits that I need to be able to isolate within my pivot table. As a simple example, my data set looks something like this:

ID	Item	Red	Green	Blue	Yellow
1	Toy Truck	1	0	1	0
2	Teddy Bear	1	1	0	0
3	Action Figure	1	0	0	1
4	Plastic Sword	0	0	1	0

<tbody>
</tbody>

Obviously an oversimplification, but Christmas toys are more fun than the data I'm actually looking at

I need to be able to filter my pivot table data based on the color of the object. Some objects will be counted in more than one category. I could get the functionality I want by creating a separate slicer for each flag column, but there are enough available values that the interface starts to look ridiculous.

Ideally, I would like to have one slicer, with values of "Red," "Green," "Blue," and "Yellow" and have any of the rows that match the current selections be counted in the pivot data. For example, in the table above, if "Red" is selected, rows 1, 2, and 3 should all be included in the calculated data. Likewise, if "Blue" is selected I should see rows 1 and 4.

The problems I've run into are related to the fact that some rows can match multiple values. I tried changing my data set to aggregate all of the flags into 1 column so I can slice the data based on that, but when I have multiple flags associated with one row, I end up having to bucket them all into a "multiple" category that skews the data.

I also tried creating a separate table that has a list of IDs and colors (with multiple rows for each ID that has more than one color.) I then linked the tables together in powerpivot and tried filtering that way, but without including the new table in the pivot table itself, the related ID field doesn't get filtered properly.

I'm kind of at a loss. In SQL, what I'm trying to do is an exceptionally simple query, so I'm having a hard time wrapping my brain around how to proceed in excel.

Has anyone tried to do something similar before?

Thanks in advance, and let me know if I need to clarify anything.

 

[sheetspread]

Are you actually pivoting anything? Or just using the pivot table as a subtotaler and filter (those functions are available outside the pivot table)? If what you've posted is the source data then you could unpivot it with this method: https://www.youtube.com/watch?v=xmqTN0X-AgY then re-pivot with the color as a single field. Keep in mind that the slicer would then have each color, but would hide those color columns not chosen--as would the column filter--and still show the color rows equaling 0. A third option is to use SQL in Excel, via either MS Query, which works on most Excel versions, or Power Query if you have BI Tools.

 

[himitsu]

The data set that I'm working with has multiple columns for cost and counts. I'm using the pivot table to get averages (for both the cost and counts) per ID by category, and I like the functionality that lets the user view the raw data set by double clicking the counts in the table.

The problem with expanding the rows to put the colors all in one category is that it messes up the overall counts by adding duplicate rows. And if at all possible, I'd like to keep the queries dynamic so that they can change the filter for the data on the fly, without having to update a query or manually filter all the flags. That works for me and my boss, but the suits aren't going to want to look under the hood.

I might be asking for the impossible, but I've seen some pretty wizardly things done in excel so I'm keeping my fingers crossed.

Thanks much for the suggestions.

 

[sheetspread]

Excel 2013 and later has a unique count in the pivot table, not sure if that helps.