How to filter the top N values per category in Tableau

A guide to filtering the top sub-category values per category.

#Tableau
#HowTo

In this example, City is ordered by the Sum of Sales, and the goal is to keep only the top 3 cities under each State/Province as highlighted below.

We’ll then go on to add a parameter so that the top N cities can be selected by the dashboard user.

Step 1

Create a new calculated field called Index

Create a calculated field called Index, and include the table calculation function INDEX() in this field.

Once you’ve created the Index calculated field, convert it to discrete.

If you don’t do this, it will give you an axis instead of a header when you add to the view, which is not what we need.

Step 2

Add Index to the Rows shelf

You can then see what values INDEX() will give you in the view. But there’s a problem, the Index value doesn’t reset to 1 for each new city under England, Scotland and Wales.

In order to correct this, on the Index pill, select Compute Using > City. we used City here as it’s the innermost dimension on the Rows shelf - choose the equivalent field in your view.

Now you can see that the Index resets for each state/province. This index is what we’ll use to filter for the top N rows.

Step 3

Filter on Index

Only do this step if you want a fixed number of top rows to be displayed per category. If you’d like the user to be able to decide how many to show, move on to step 4.

Simply filter on Index and select the top numbers. In our case, select 1, 2 and 3 to keep the top 3 cities.

Remove Index from the Rows shelf, and you’ll have the view you’re looking for:

Step 4

Create a new parameter

Do this step if you want to allow the dashboard user to select how many top N values to show per category.

Set up the parameter Top N as below. This is what the user will use to select how many top cities they want to display.

Step 5

Create a calculated field

Set up a new calculated field called Show top N with the formula below:

[Index] <= [Top N]

This field will be a boolean with a value of True when the row number or Index is lower than the Top N parameter.

Step 6

Filter on Show top N

Drag the Show top N calculated field to the Filters shelf and keep only values where it’s True.

In case it isn’t already set up correctly, set the Show top N filter to compute using City, or the pill furthest to the right on your rows shelf - similar to step 2.

Now the view is showing the top city for each state/province as the Top N parameter was initially set to 1.

It’s now fine to remove the Index field from the rows shelf as it’s no longer required.

Step 7

Show the Top N parameter

Once you show the Top N parameter, users can then change this value as needed.

And there you have it! Users can now select the top N values per category in your view.