<< Back

(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’.

create set

 

Created a Top 10 based on Sales

set specs

 

Then create a calculated field to create the ‘other’ label for the chart:

set label calc

([Top 10 States by Sales] is the set we created)

When put in to a view we end up with this viz:

set view

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:

map view

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):

dashboard action

Here’s my dashboard:

 

broke it!

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.

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!

final dashboard

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.

Emma Whyte

London, UK

7 thoughts on “(Almost) Dynamic Sets in Tableau

  1. Good tip. I am trying to extend this pattern to where i can click on the “Other” bar and the bar chart filters itself to the components that constituted “Other” bar.The top ten will now be calculated on the components of “Other” bar.

    In effect inline drill down while limiting the no.of components you are showing(paging concept). I can do the inline drill down seperately. I am looking for pointers to combine your technique with inline drill down to effectively show high cardinality attributes.

  2. I knew it! I thought it would be something like this. Experimented didn’t get it. Looked at a few other options and then found this post. Thanks for saving me a heap of time!

Leave a Reply

Your email address will not be published. Required fields are marked *