(Almost) Dynamic Sets in Tableau
I had an interesting request recently: to display a top 10 list including ‘others’ that updated depending on what was selected on the dashboard.
The first obvious solution was to use a Set which only included the top 10 but displayed the outs as ‘other’. This was done as follows (re-created with Superstore Sales, of course).
Right-click States in Dimensions and select ‘Create Set’.
Created a Top 10 based on Sales
Then create a calculated field to create the ‘other’ label for the chart:
([Top 10 States by Sales] is the set we created)
When put in to a view we end up with this viz:
In the view I have placed the Set ([Top 10 States by Sales]) and our [State Labels] calc on the columns shelf. Sum of [Sales] goes on to columns. I have also decided to colour the bars by profit and changed the colour palette to blue.
Okay this looks good so far. Next I made a quick map to place on a dashboard. This shows sales and profit by country/region:
I thought I could then just set up a dashboard with the map and bar chart and use a filter Dashboard Action. When a user selects a country on the map I wanted the bar chart to show the top 10 states/countries within the country/region selected. However, I didn’t get the result I expected.
To set up the Dashboard Action (click on Dashboard in the toolbar – select Actions. Then add a filter action as follows):
Here’s my dashboard:
If I selected a country/region (I selected United States), rather than the set updating to the top 10 states in the US, the Dashboard Action is filtering my set to show only those in the top 10 in the US. How do I get around this?
To understand how to solve this problem, we have to know what Tableau is doing in the background. Tableau is computing the set before the filter. We have to force Tableau to work out the filter first, and then make the top 10 set based on the results of the filter.
Context filters to the rescue!
Tableau follows an order in its processing. Context filters will always be calculated before any other filters, sets are computed after a context filter also. The great thing about dashboard actions is that they appear on the filter shelf in the sheets that are using the dashboard action.
If we go to our bar chart after the dashboard action is applied, we can then make the action filter a context filter.
Now when we go back to our dashboard and select a country on our map, the bar chart will update to include the top 10 states by sales for that country. Hurrah!
So there we have it. A set made (almost) dynamic by using a simple dashboard action turned context filter. Thanks to a little bit of knowledge of what Tableau is doing in the background.
You can download the packaged workbook for this example here.