[Tableau] Building Hierarchical Drill-down Dashboard
Detailed instructions on how to build a Tableau dashboard to show hierarchical metrics with drill down capabilities
Recently, in the course of my work, I came across a visualisation requirement — showing high-level KPIs (L1 metrics) for customer journey and then being able to drill down to corresponding secondary metrics (L2 metrics ) for each L1 metric. I have explained about L1, L2 metrics and overall metrics framework development in my previous blog. The problem, with the dashboarding requirement, was that the L1 -> L2 linkage was only per business logic; meaning L2 metrics did not numerically (aggregated or otherwise) contribute to L1. It was just business logic that defined which metrics were to be considered L1 and which should be their associated L2 metrics.
For example, in e-commerce, there is a metric called pick rate which signifies how many of the items from the customer’s order can be successfully fulfilled from the store/warehouse. The pick rate is considered as high-level L1 KPI metric. In case some items can’t be fulfilled because they are out of stock, the customers are offered a substitution (another item similar to what they had ordered, but different brand etc.). If they don’t accept substitutions, then those out of stock items are cancelled from the order and the balance money for those items is returned to customers. Thus the two metrics, substitution rate & cancellation rate, don’t numerically contribute to the pick rate numbers but are considered, per business logic, related L2/secondary metrics since all three are part of the whole “item picking” step.
While I was trying to build drill-down dashboard in Tableau, almost all of the solutions I found on internet had two problems w.r.t my scenario—
- they consisted of defined hierarchy in data itself (e.g. city -> state -> country), while my metrics hierarchy was logical and didn’t reflect in the data
- for most of the drill downs, clicking on top level chart would change the view to the next level of granular chart. My requirement was that L1 and L2 (and possibly L3 in some cases) metrics and the flow between them should be on same page in the same view. Also every L1 had its own chart to be displayed and we should be able to list the different L2s associated with that high-level metric too. Viewers can then go and select the L2 they wanted to see or switch between different L2s while the high-level L1 view remains unchanged.
Finally after lot of iterations, I built a working prototype. I have built a similar dashboard here with a Kaggle dataset. I have detailed the steps involved, below.
The Kaggle dataset consists of few metrics recorded over 2017 and 2018 for various cycling activities like indoor cycling, road biking and cycling. I randomly assigned few metrics as L1 metrics and few others as L2 for each type of activity. Usually, in real world, the business may dictate which ones are the most important KPIs they want to track and which would be secondary, less important granular metrics. I listed the L1 and L2 metrics in a separate Excel called “Counter.xlsx”. This file would be needed later while building the dash. The structure of Counter.xlsx is as below —
The individual charts I built are pretty standard, so I will jump straight to the dashboard part where all the action happens. The whole drill down navigation effect happens due to containers in Tableau. Getting the container placement right was the most difficult part in building this dash, that was the key. I have shared a snapshot of the final container placement below-
The container layout should consist of an overall container which holds four horizontal containers, one each for the L1 metric selector navigation panel , the chart space for selected L1 metric, the L2 metric selector navigation panel and the chart space for selected L2 metric. I started with just getting this container layout structure right before adding any charts or any other components. This is how the initial bare layout looked like-
After that, using the Counter.xlsx as data source, I pulled the metrics field and filtered for only the L1 metrics. The MIN(1000) in the columns, gives it the bar structure.
I added this chart in the first of the four horizontal containers. This would help in navigating through the top level KPIs.
The collapsible container effect is done through parameter actions. For that we need to create a parameter “Chart Type” which will help in selecting the L1 metric. One important point to note, the name of the metrics listed under the “List of values” in the parameter properties here must match the names in the “Counter.xlsx”.
Next I created a calculated field called “L1 chart type” and pulled the “Chart Type” parameter as the field calculation.
For all three L1 charts, I clicked “Show Parameter” option next to the “Chart Type” parameter. For Road Biking Distance chart, for e.g., I selected the parameter value “Road Biking (Distance)”. I added “L1 chart type” field as the filter in the chart and selected “Road Biking (Distance)” value in filter. I did this for other two L1 charts too. This ensures that when that particular L1 is selected through navigation bar, the parameter changes and the filter will show only the desired chart.
In the dashboard, I added the three L1 charts one next to another in the second horizontal container.
There should be only one chart displayed in the entire horizontal area depending on which L1 metric is selected from top navigation. The two most important things to remember here — the titles for the three charts should be hidden and none of them should be of fixed width (the pin icon next to the chart circled in red in the above screenshot). Once these two things are taken care of, the containers collapse and only one L1 metric chart is visible. To be able to change the chart on clicking the tabs in the navigation bar, we need to setup Dashboard actions. Clicking on the “Dashboard” tab at top tool bar will show the “Action” option. Once the Actions box pops up, select “Add Action” -> “Change Parameter”.
Next I updated the Parameter Action box as below. Here L1(Metrics) is the chart with the navigation bar and Metrics1 (Metrics) is the metrics field from the Counter.xlsx dataset.
On adding this action, now L1 metrics navigation bar should be clickable and selected chart should appear. Next is the L2 metrics bar. Here the complication is some L1 may not have any associated L2 metric, some may have 1 or more depending on the business logic.
We need to create separate navigation bar charts for each L1 since we want to dynamically change the L2 list depending on which L1 is selected. Below I have shared how the L2 navigation for Indoor cycling L1 looks like. From the source Counter.xlsx I selected associated L2 metrics, clicked “Show parameter” option for “Chart Type” parameter, selected “Indoor Cycling (Training Score)” as the parameter value and added the calculated field “L1 chart type” as the filter. Please note we can’t add MIN(1000) in columns in this case to get the bar shape like before, because that won’t allow the bar to collapse (fix its width) and we want collapse effect for L2 bars for dynamically changing L2 listing. Please take a note on the Marks settings as well.
I added all the L2 navigation charts side-by-side in the third horizontal container and removed the chart titles and ensured none of them are fixed width. Depending on which L1 is selected, the rest of the L2 bars collapse and are not visible.
I created another parameter “Indoor Cycling L2” as below.
Please note the additional value “Blank” added in the list of values. Again, the names of L2 metrics listed here should match the names as they appear in Counter.xlsx. I created similar parameter for “Cycling” too and for every L1 metric which has associated L2 metrics. For example, I selected “Power” as “Indoor Cycling L2" parameter value for power chart. I created another calculated field wherein I added the “Indoor Cycling L2” parameter as calculation. I used this field as filter in the chart (same as we did for L1 charts).
Next I added all the L2 charts (all of them irrespective of which L1 they belong to) in the fourth horizontal container at the bottom. I hid the chart titles and removed fixed widths. I added one dashboard action for each L1 metric which has L2s associated with it.
The Source chart in this case should be the corresponding L2 navigation chart. Please note the “Clearing selection will Set value to” “Blank” setting. This is very important so that when a particular L2 metric is selected the rest of the L2s from its family don’t show up. For cases where some L1 doesn’t have associated L2 metric the bottom 2 horizontal containers dedicated to L2s will remain blank (check “Road Biking (Distance)” L1 in my dashboard).
I have also added “How to Use” instructions button. Clicking on the button will cause a pop-up to appear which details how to read the dashboard. For the pop-up effect I used a floating container, changed the background colour and added an image in the container. I had listed the instructions in a PPT and created an image out of it. Clicking the dropdown next to the floating container I selected “Add Show/Hide Button”. I updated the button to show nothing when “Item Shown” and button image when “Item Hidden”.
I correctly overlaid the floating container so that the cross button appeared on top corner.
Hope you try this kind of drill down dashboard. Let me know in case of any questions/ suggestions in the comments.
