• Bernard

Tableau set action - asymmetric drill down


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.