Grouping with Hierarchies re-visited

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

1. Create a Tableau group based on Salesperson, grouping salespeople into territories: Western, Central, Eastern, with an additional group for No Salesperson.
   

   

2. Create a hierarchy with the Salesperson Group as the highest level.

   1. 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.
      

   2. Make copies of the Customer, Inv No, Invoice Date, and Item fields to add to the hierarchy.
      Since the relationship between Inv No and Inv Date is a one-to-one relationship, you can save a step by creating a calculated field of the Inv No field and Inv Date by converting the Inv Date field to a String value and concatenating it to the Inv No field.

      

   3. The end result is a hierarchy going from Salesperson Group to Item.

      

3. 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

   

4. 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.

1. 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.

   

2. 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.