How To: Dynamic Drill-Down Using a Heat Map Selection
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:
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.
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.