We know that in Tableau we can easily drill down and up dimensions in a hierarchy by clicking the plus sign (+) or minus sign (-) on the fields. For example, in Superstore data, we can drill down Category to Sub-Category level. In this case, if you choose to drill down, all sub-categories in each category will be shown.
But what if you only want to drill down to sub-category level of a selected category? Like in the example below, only sub-categories in Office Supplies category are shown because Office Supplies is selected. And the other categories remain aggregated because they are not selected.
This can be done by using set action. In this blog I’ll show you how to do this step-by-step by using Superstore data. Let’s get started!
Step 1. Create Category Set
First of all, we need to create a category set. Click on Category field on the data pane. Select Create, and then select Set. In the pop-up window, you can choose any category in your set. This doesn’t really matter as we will be updating the set later. After this, clink OK.
Step 2. Create Partial Drill Down Sub-Category Field
Next, we need to create a calculated field to show only sub-categories in Category Set. The formula used is saying: If a Category is in the Category Set, then return Sub-Category value. Else just leave if blank.
Then drag Category and SUB-CATEGORY (the calculated field we just created) to rows, and a measure to columns. Also drag Category Set to color on marks card. Now your view should look something similar to below.
Step 3. Use Set Action to Make it Dynamic
The next step is to make the view interactive by using set action. To do that, select Worksheet–> Actions. In the pop-up window, select Change Set Values.
In Add Set Action window, select Category Set as your Target Set. Then depending on the user experience you’d like to create, different options could be selected in the last part. Here I’d like the user to drill down sub-category by clicking a category, and drill up everything to category level when the selection is cleared. Therefore, I choose “Assign values to set” when running the action, and “Remove all values from set” when clearing the selection.
Now the result should look something like this. We are now able to click different categories to drill down its sub-category.
However, you may notice that the bars of those unselected categories are de-highlighted. This can be fixed by using a simple highlight action.
Step 4. Fix the Highlight
To do so, first we need to create a dummy field. I’ll just call it “Dummy”. Then drag this dummy field to detail on marks card. We can also deselect “Include in Tooltip” so users won’t see it on the view.
Next we need to create a highlight action. Similar to creating set action, go to Worksheet–> Actions. This time we are selecting Highlight. In the pop-up window, choose the dummy field you just created as the target highlighting field. Then click OK twice.
Here we have it! Now we can drill down only the selected category without de-highlighting the rest.
This tip could be useful when you have a lot of fields in your drill down but you also want to save some space on your dashboard. Or you want to allow the user to only focus on one drill down at a time.
I hope you find this blog helpful!