How To Create Action Filter on Worksheet with Top N Parameter

I’m pleased to present the inaugural post of a ‘How To’ series, with the intention to share the endless Tableau tricks and tips.  This post will explain the nuances of how to create an action filter on a worksheet where there’s a top N parameter.

For reference on how to filter for top and top N, please review this excellent video from Tableau that shows how INDEX() function is used for ranking.  However, in the video, top N is selected as a fixed number. 

To take this one step further where top N is a parameter, I used Superstore data to create this visualization.  Here’re the steps:

1) Create a ‘Top N Customers’ parameter.

2) Create a calculated field ‘Rank’ using INDEX() function to rank for top N customers.

3) Create a ‘Top N Filter’ calculated field where [Rank] <= [Top N Customers] to show top N customers.

4) Drag the ‘Top N Filter’ to filter pane and select ‘True’.

5) Click on ‘Edit Table Calculation’, in ‘Compute using’, select Advanced.  Move State and Customer over to Addressing pane.  Under Sort, select ‘Field’ and select ‘Profit/Sales’ calculated field. Then click OK to exit ‘Advanced’ window.

6) Back in ‘Edit Table Calculation’ window, select ‘Customer’ in ‘At the level’ and ‘State’ in ‘Restarting every’.  This will calculate at the customer level and restart for every state.

Since the map will be used as a filter, it’s important to make sure that every state is visible.  I decided to go with the hexagonal tile map (read Matt Chambers’ post for instruction).

The last step is to create an action filter where clicking on a state will show the top N customers by profit or sales for that state.