Have you ever wanted to just create a drill down table in Tableau, but all the solutions you found either added additional columns for each subsequent level you drill down to OR removed the subtotal/parent level when you drilled down, OR took a huge toll on performance when you incorporated it? If yes, then you have come to the right page.
In this blog, you will learn a practical and efficient way to create an interactive drill down table in Tableau (like the one you see below).
Please note that to get the drill down table to look like that, a few calculations from Tableau Visionary Rosario Gauna’s amazing blog on a related topic will be used here.
Dataset
You can use the Orders table of the latest (2020-2023) Superstore dataset to follow along.
Please note that as long as you have at least the same number of rows at the lowest level of your hierarchy as the number of levels required in your drill down table, you do not need data densification—the process of adding multiple rows with specific columns for every row in raw data.
In this case, there are at least 4 rows for every Month+Year.
That being said, it is generally safer to use data densification so that things do not break due to the changes in the underlying data over time.
To do the data densification, add a table in relation to a relationship calculation of 1 = 1, as shown below. This will give you 4 rows (with Levels as 0, 1, 2, and 3) for every row in the Orders table.
Step 1: Creating a Basic Hierarchy: Nested List
To start with, create calculations for getting the years, quarters, and months in string format. You will not need to do this if your hierarchy is composed of string dimensions.
//Years
DATENAME('year',[Order Date])
//Quarters
"Q" + DATENAME('quarter',[Order Date]) + "-" + RIGHT(DATENAME('year',[Order Date]),2)
//Months
LEFT(DATENAME('month',[Order Date]),3)+ "-" + RIGHT(DATENAME('year',[Order Date]),2)
Next, create a calculation called “Level,” as shown below. This calculation will not be needed if you are using data densification. Convert it to dimension.
//Level
DATEPART('day',[Order Date])%4
To test if the “internal densification” works properly, drag Months and Level to Rows.
It does! For the 48 Months, there are 192 marks (48×4) in the view.
Now, to build the hierarchy (nested list), create the following calculation.
//Hierarchy
CASE [Level]
WHEN 0 THEN "All Months"
WHEN 1 THEN "All Months" + " >> " + [Years]
WHEN 2 THEN "All Months" + " >> " + [Years] + " >> " + [Quarters]
WHEN 3 THEN "All Months" + " >> " + [Years] + " >> " + [Quarters] + " >> " + [Months]
END
Drag this to Rows to see what it looks like.
Notice that since this is sorted alphabetically, the Months in the Quarters are not in the right order. To fix this, create a Custom Date with the Order Date field and use it to sort the Hierarchy, as explained in the following images.
This Hierarchy will act as the base for the entire drill down table.
Step 2: Developing the Expand/Collapse Feature
To keep track of the node that needs to be expanded and collapsed, create the following calculations.
//Header
CASE [Level]
WHEN 0 THEN "All Months"
WHEN 1 THEN [Years]
WHEN 2 THEN [Quarters]
WHEN 3 THEN [Months]
END
//Header -1
CASE [Level]
WHEN 1 THEN "All Months"
WHEN 2 THEN [Years]
WHEN 3 THEN [Quarters]
END
//Header -2
CASE [Level]
WHEN 2 THEN "All Months"
WHEN 3 THEN [Years]
END
//Header -3
CASE [Level]
WHEN 3 THEN "All Months"
END
Drag all these in Rows next to Hierarchy, and this is what you should have.
If we consider Header as the child node then, Header -1 is the parent node, Header -2 is the grandparent node, and Header -3 is the great-grandparent node.
In order to expand and collapse the drill down table when the user clicks on it, you will have to use a multi-select parameter action. For that, create a parameter as per the following image.
To define what gets passed in the parameter when the user clicks on any row of the hierarchy, create this calculation.
//Value
IF [Level] != 3 THEN
IF CONTAINS([Drill Down], "~" + [Header] + "~")
THEN REPLACE([Drill Down], "~" + [Header] + "~", "")
ELSE [Drill Down] + "~" + [Header] + "~"
END
END
The above calculation will add Level 0, 1, and 2 nodes in the parameter if they are not already in it and remove it if they are. The tilde at the start and end of each value ensures that each Header that gets added is delimited.
This becomes necessary if two nodes in your hierarchy share a common word. For example, North Dakota and South Dakota.
Next, Add Value in Detail and create a parameter action on the worksheet as per the following image.
Show this parameter on the worksheet and observe how the values get added and removed in the parameter.
However, the whole tree is visible at all times, no matter what gets added/removed from the parameter. To only show the correct nodes, create this calculation and add it in Filters with True selected.
//Filter
CASE [Level]
WHEN 0 THEN TRUE
WHEN 1 THEN CONTAINS([Drill Down],"~"+[Header -1]+"~")
WHEN 2 THEN CONTAINS([Drill Down],"~"+[Header -1]+"~") AND CONTAINS([Drill Down],"~"+[Header -2]+"~")
WHEN 3 THEN CONTAINS([Drill Down],"~"+[Header -1]+"~") AND CONTAINS([Drill Down],"~"+[Header -2]+"~") AND CONTAINS([Drill Down],"~"+[Header -3]+"~")
END
Note that different conditions for each level are needed to avoid blunders like child nodes being visible when a grandparent node is removed. Also, if a top node is collapsed, the expand/collapse states of the nodes underneath it are preserved.
With this, you now have a bare-bones drill down table!! Play around with it.
Step 3: Formatting the Drill Down Table
To get the Hierarchy labels indented with the right symbols, create the following calculation.
//Label
SPACE([Level] * 3)
+
IIF([Level]=3,"●",IIF(CONTAINS([Drill Down],"~"+[Header]+"~"),"▼","▶"))
+
" "
+
[Header]
The first part will indent the text based on the Level. The second part will add “●” against all the Level 3 entries (Month+Year), “▼” for the expanded nodes, and “▶” for all the collapsed nodes.
Remove the four Headers, untick Show Header on the Hierarchy, and drag the Label in Rows next to Hierarchy. This is what you’ll get once you do that.
It doesn’t look aesthetically appealing in this state. To have more flexibility with the formatting, you will need to use placeholders.
To do that, double-click on the Columns shelf and write 1. Convert this to a dimension. Then add the Label in the Label part of the marks card and change the mark type to Bar.
You will end up with this.
Now, edit the axis as per the following image and untick Show Header.
Increase the bar size to the maximum value, add Level to Color, tweak the font (choose a mono font for best results) and align it to the left. You should end up with something like this.
If you are using data densification, you can use the vanilla Sales field. But here because of the internal data densification, you will have to use the following calculation to get the right value.
//Drill Down Sales
{ FIXED [Years], [Quarters], [Months]: SUM([Sales])}
Ctrl+Drag the 1 field next to the 1 field in Columns and replace the Label with Sum(Drill Down Sales) in the new 1(2) card. You can customize the label and sheet to your liking until your drill down table looks like the one in the following image.
The only problem is that when you click on a month, the highlighting makes a mess. To fix it, create a calculation like shown below.
//Highlight Disabler
“x”
Drag this in the Detail of the All marks card and do the following.
With that, you are all done!!
Conclusion
Hopefully, you enjoyed learning this new technique to create fully functional Drill Down tables in Tableau that are not too hard on your system resources!
If you want more information on Drill Down tables in Tableau, contact our team of experts!
