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.

8 comments:

  1. Hi Matt. I apologize for commenting here as it's unrelated to this viz, but from Tableau Public -> Contacting you is a very hard link to make. Anyways please check out my viz at
    https://public.tableau.com/profile/clinton.johnson6689#!/vizhome/LeagueofLegendsStronkestADCs2/Dashboard1
    it has 1000 views in 2 days, looking to get on a Viz of the Day or something...
    Thanks!
    Clinton Johnson

    ReplyDelete
    Replies
    1. Hi Clinton! Thanks for reaching out! This is a really cool viz. Are you on Twitter by the way? Most people share Tableau content through Twitter. I'll send it in and we'll see if it gets Viz of the Day. Where did you share it to get so many views?

      Delete
  2. Matt, thanks for the great idea. However, though I can replicate your viz, I cannot get the State variable in the title bar on the target worksheet to update. Any pointers on steps to avoid or steps to never overlook would be appreciated.
    I'm in an organization just starting with Tableau. I'm like a kid in the candy store at the moment checking out and attempting to imitate all the great ideas out there. Below is the link to my attempt at your viz:

    https://public.tableau.com/shared/3HZ58CYKR?:display_count=yes

    ReplyDelete
  3. OK, I was able to work through my issue by downloading your viz. Now I'm finding that I cannot get the sub-category values to display when I select a State. I'm thinking I took a wrong turn somewhere with the filters, as mine look a little different, though I do not understand why the data results work but the Sub-Category headers don't show while the Category headers remain. Though I've used the calculation, it seems I can't get past the traditional look. Any pointers would be greatly appreciated.

    https://public.tableau.com/profile/publish/HeatMap_as_Filter/WorksheetHeaderFailureCorrection#!/publish-confirm

    ReplyDelete
    Replies
    1. Hi Kevin, thanks for reaching out! I just noticed that the subcategories show All in mine as well. I looked through your example, and it looked like you were able to fix it. Is that the case?

      Delete
  4. I did get further along. However, I'm rather perplexed by the behavior of display of the sub-category header. In yours, the Category header displays as the only visible header, but when you select a state/category on the heat map your visual hides the Category header and only displays the Sub-Category header. I noticed in yours the worksheet for the Sub-Category data has the header not displayed, which I tried. My results, though, are that when nothing is selected Category headers shows, but when I select on the heat map, the Category header remains but displays only one value across what are obviously multiple sub-categories. I published a version and exposed all my sheets.

    https://public.tableau.com/profile/publish/HeatMap_as_FilterDashboard/ProfitByStateCategory#!/publish-confirm

    ReplyDelete
  5. And many thanks, by the way, for lending a helping hand...

    ReplyDelete
  6. i need help on creating drill down map for uk .....PLease connect to me Vinay120kumarp@gmail.com

    ReplyDelete