Combining Groups & Hierarchies for custom drilldown lists.
This is a simple method that expands the ability to expand from summary data to detail level within the same worksheet. This was shown in Orange Belt training, but it’s worth reviewing again. The examples assume you already know how to create groups and hierarchies.
Example 1: Group with Hierarchy Drilldown.
Activity 1: Create a new workbook connected to the _Sales_Invoice data source with a Territory group (of salespeople) and a hierarchy drilldown to customer, invoice#/date, item.
Link to example worksheet: 01 Group Hierarchy with Drilldown: Example 01 Group & Hierarchy - DataSelf Analytics
Create a Tableau group based on Salesperson, grouping salespeople into territories:
Western, Central, Eastern, with an additional group for
Create a hierarchy with the
Salesperson Groupas the highest level.
The Salesperson is the next hierarchy level. Create a duplicate of the original field and add a dash (“-”) to the end of the name. Since a specific field name can only be in one hierarchy, creating a copy leaves the original field in its current hierarchy relationship.
There was nothing special about adding a dash. You can use whatever works for you to differentiate the field name.
Make copies of the Customer, Inv No, Invoice Date, and Item fields to add to the hierarchy.
Since the relationship between
Inv Dateis a one-to-one relationship, you can save a step by creating a calculated field of the
Inv Nofield and
Inv Dateby converting the
Inv Datefield to a String value and concatenating it to the
The end result is a hierarchy going from Salesperson Group to Item.
When displaying the Salesperson Groups, the default sort is alphabetical.
To show in the order: Western, Central, Eastern, No Salesperson, use the Manual Sort feature
To show totals for the higher hierarchy levels when drilling down, Add Subtotals.
The Subtotals disappear as you collapse the hierarchy.
Example 2: Hierarchy that Collapses to the Summary Grand Total
Activity 2: Create a new worksheet and hierarchy with the Grand Total as the highest level.
Link to example worksheet: 01 Group Hierarchy with Drilldown: Example 02 Grand Total & Hierarchy - DataSelf Analytics
The trick with this idea is to create a calculated field with a constant value as the highest level in the hierarchy. Since all records have the same value, they collapse to one row. The total is the same as the grand total.
Create a calculated field with a constant value in it. The example below shows a calculated field called Summary with the string value of “Grand Total”. You can make the string value whatever you want to describe its purpose. The field name will be the column header.
Create the same hierarchy as described above. If needed, duplicate fields and assign modified names to avoid the field duplication issue. The result is that when the hierarchy is collapsed to its highest level, you only see one row with the grand total.
3. The hierarchy expands from Grand Total to Salesperson Territory with the subtotal showing the grand total value.