About a week ago, I shared a blog post that demonstrated a new way to visualize Key Performance Indicators (KPIs) in Tableau. This week, using the same viz, I’ll show how to set up single-click hierarchy navigation.
The Challenge
Those of us familiar with Pivot Tables in Excel appreciate a good hierarchy. The good news is that many of those concepts translate to Tableau, but there are some differences. We can drill down in both, but Excel expands a single line. Tableau expands all lines when using a hierarchy to drill down. If we have a long list when drilling down, this is a problem. There is a method in Tableau where we can click a line, select Keep Only, drill down and then repeat on down the list, but that can get a bit tedious. So, what to do?
Common Approaches (Short and Sweet)
Fortunately, as you might’ve guessed with Tableau, there are some other ways for us to set up manageable hierarchies. Here are two of the more common approaches along with their drawbacks:
Solution 1: Use action filters on different sheets with different levels of detail, top-to-bottom.
Drawback(s): Maintaining consistency across all sheets is hard. Really deep hierarchies also result in way too many sheets.
Solution 2: Use quick filters and change hierarchy level with parameter selection.
Drawback(s): This requires lots of clicks.
My Solution
The above solutions can certainly work, but they’re not perfect. After messing around in Tableau to maybe find something better, I created what I feel is a more elegant solution to achieve the desired results.
First, let a LOD calculation choose what level to display based on quick filter selections. Then, plug in my “Dynamic Header” calculation below.
Here are a few things to keep in mind concerning this calculation:
- When only one thing from the higher level is selected, it shows the next level down.
- We MUST start at lowest level of hierarchy in the IF statement. If we start at the highest level, choosing one value from Level2 will still choose to show Level2 because of the logical order. Since Level1 ALSO has only one value selected, it will think you want to show Level2.
- This MUST be LOD because we’re changing the granularity (literally the level of detail) of the view. COUNTD() must be computed for the whole data set, not each row or cell. Also, without LOD, we’d have to include the lowest level (Level3) in the view somehow, which doesn’t really work when we want to summarize at a higher level
- This MUST be FIXED type because it can be used like a dimension field. INCLUDE and EXCLUDE are treated more like aggregated measures.
- CAUTION: We must use context filters to affect fixed LOD calculations. Right-click the filter and choose add to context for any part of the hierarchy used in the calculated field
The Result
Below is the resulting viz with our new single-click hierarchies in action. Managers are Level 1, Investment Managers (IMs) are Level 2 and Advisors are Level 3. Pick a manager and an IM to see how it dynamically displays the appropriate level of detail depending on the selection. Enjoy!
