How To: Dynamic Drill-Down Using a Heat Map Selection

12/01/2015 Matt Chambers 8 Comments

I'm a big proponent of using a heat map as a filter to control a dashboard.  This gives you the functionality to be able to click a row, column, or individual cell in order to filter a dashboard.  Recently, a coworker and I were working on a dashboard, and we wanted to be able to drill-down from Category to Sub-Category using a heat map.  For this example, we will use the Superstore data to illustrate our solution.

It's simple enough to create a heat map and a dashboard action to make it filter another chart.  The original solution looked something like this:

There's really nothing wrong with this solution, but we wanted to be able to see the totals for the categories and then drill-down to the Sub-Categories.  To do this, we need to be able to detect the click on the heat map and drill-down to the appropriate Sub-Category.  Let's take a look at how this can be done using Tableau.

First, set up the Category/Sub-category worksheet.


Next, we need to be able to determine if a Category has been selected in the heat map.  To do this, we can utilize a Level of Detail calculation to exclude the Category and Sub-Category from the view and count the distinct Categories.  If there is only one Category, we know that we should sum the Profit for the Sub-Categories, and if there is more than one Category we will total the Profit for all Sub-Categories.


Once we have created the calculation, we can now replace the Profit measure.  We need to make sure that the calculation is computed using the Sub-Category.


We will also need to know which Dimension is being displayed when the heat map is clicked, so we will use the following calculation.
Once we have added this new calculation to the Rows shelf, we have the following view.


Now we can see that we are showing the Category when there is no selection made, but we need to be able to deal with the duplication.  In order to do this, we detect if a Category has been selected and return the Index if it has.  If there are no Categories selected, we simply return a 1.


Then, we need to be able to use this calculation to filter the results. This will remove the duplication we encountered earlier.



We will then need to filter to at most one value to remove the duplication for Categories and show the values for each Sub-Category.


Now we can hide the headers for the Category and Sub-Category, which gives us the totals for each Category when the heat map is not selected.



Next, we can add both sheets to the dashboard and create the dashboard action to filter the bar charts by the heat map.


We now have our working dashboard.  When no selection is made, the Categories are totaled.



However, once we make a selection on the heat map, the bar charts drill-down to the Sub-Categories.



I have uploaded the sample workbook as well.  Feel free to download and explore.