How to Set Up Single-Click Hierarchy Navigation in Tableau


How to Set Up Single-Click Hierarchy Navigation in Tableau

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:

  1. When only one thing from the higher level is selected, it shows the next level down.
  2. 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.
  3. 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
  4. This MUST be FIXED type because it can be used like a dimension field. INCLUDE and EXCLUDE are treated more like aggregated measures.
  5. 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!

More About the Author

Robert Rouse

Analytics Consultant
Eine neue Art KPIs in Tableau zu visualisieren Key Performance Indicators sind eines der Dinge, die Leute am häufigsten in Tableau darstellen möchten. Das bezieht für gewöhnlich ein, ...
Disney Data & Analytics Conference 2019 in Review At Disney World, every park has a theme. What you may not know is that their annual data and analytics conference has a theme, too. In ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK


Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Geschäftsführer: Mel Stephenson

Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072


Love our blog? You should see our emails. Sign up for our newsletter!