The problem
When working with data that has hierarchical information such as product category, sub-category, manufacturer and product name, you will notice that when drilling down a hierarchy all values expand at a given level instead on a single value.
(In the above example, drilling down reveals all sub-category members, when in the actual sense you might be interested in ‘Technology’ sub-categories only)
The solution with Tableau set actions
Using Sample-superstores dataset, lets apply set actions to create a symmetric drill-down experience – enabling users drill-down (category to sub-category to manufacturer)
Step 1: Create a set for Category field named Category set, by choosing any category to be a member of the set.
(Note: The current membership is temporally and will be overwritten by the set actions)
Step 2: Create a calculated field named ‘Asymmetric Sub-category’ as shown below.
Note: This calculation will be returning the sub-categories of the IN-SET (selected category), otherwise it will return the category again.
Step 3: Create a set for Sub-category field named ‘Sub-category set’, by choosing any Sub-category member of the set.
Note: This set is created from the field ‘Asymmetric Sub-category’
Step 4: Create a calculated field named ‘Asymmetric Manufacturer’ as shown below.
Note: This calculation will be returning the Manufacturer of the IN-SET (selected Sub-category), otherwise it will return the Sub-category.
Let’s build our view
Drag Category to the rows shelf
Next drag Asymmetric Sub-category followed by Asymmetric manufacturer to the rows shelf
Drag Sales to the label
(Note: The table is drilled down at the members used in Category set and Sub-category set respectively - in this case ‘Furniture’ and ‘Chairs’)
These default members will be overwritten by our set-action below.
Creating a set action
To create a set action, go to Worksheet menu >> Actions…
On the pop-up menu click Add action >> Change set values…
Creating a set action to drill-down to sub-categories
Rename your set action, in this case the first set action is renamed ‘Drill to Sub-categories’
Choose source worksheet in this case ‘Sheet 2’
Select how the action will be executed – in this case run action on ‘Selection’
Choose the target data source ‘Sample-Superstores’
Choose the target set – in this case ‘Category set’
Running the action will ‘Assign values to the set’
Clearing the selection will ‘Remove all values from the set’
Creating a set action to drill-down to manufacturers
To add another set action, select >> Add Action >>Change set values…
Repeated the above step and select the target set to be ‘Sub-category set’ instead of ‘Category set’
Executing this we’ve a symmetric drill-down which expands on a single value instead of the whole level.
I hope this article was somewhat helpful to you. To receive more of the Tableau tips and tricks, kindly join our mailing list by subscribing below.
Thank you for reading.