Tableau Top N values by category

This article will explain how to show the Top N values in Tableau; the Bottom N values; and also both the Top AND Bottom N at the same time.

There are a number of different ways to do show the top n in Tableau. The best way depends on a number of factors specific to your data and worksheet.

The simplest method is to filter for the Top N. However this can be too simple for some scenarios. Sets are another option, as are table calculations.

The same techniques also work to select the Bottom N. Scroll to the bottom of this article for the section on keeping the Bottom N and also keeping both the top and bottom N.

This article covers:

• Top N using a filter or set
• Top N using a calculated field
• Top N with multiple dimensions – a nested top N
• Show only the Bottom N
• Show the Top and Bottom N

Use a Parameter to show the Top N

It’s possible to modify all of these techniques to allow the user to choose the number of Top N (thanks for the question Mohammed in the comments!). Do this using a parameter. The article shows how to use this parameter in each section below.

Create a parameter as below, a simple integer parameter, where mine defaults to the number (top) 5.

Showing the Top N records in Tableau

Look at some examples with a very simple data set. It only has three fields; two dimensions, Category and Region; and a measure, Metric.

To begin sort the Region by Metric:

Top N by Filter / Set in Tableau

Starting with a very simple example, use a filter to show the top 5 Regions. First sort the Region descending by the sum of Metric. Therefore we can see the expected top 5 regions.

This Top B by field filter works on a simple one dimension chart.

Using a filter on a Set instead achieves the same result. Create a set on Regions and set to Top 5.

Replace the Region filter with the Set, the outcome will be the same. However a Set has other uses so worth making you aware.

For example, if you only want to highlight those regions in the top 5, while displaying all, a set is a good way to do that.

Use a parameter to alter the top N in the set

Edit the set (or filter) above and instead of entering the value “5”, choose the parameter “Top N Parameter”. Now show the parameter, change the value, and see the top N on display alter!

Top N by Calculated Field

Another technique is to use a table calculation. Either INDEX(), FIRST() or RANK can show the Top N.

For this example I’ll use INDEX, but the other options work in the same way.

Create a calculated field Index with the formula INDEX().

[Index]: INDEX()

Drag the Index field to Filters and allow a maximum of N.

Either using a Top N filter, a Top N set, or a calculated field works to show Top N on a simple view. However, what happens if a second dimension is brought into the view?

Top N with multiple dimensions in Tableau

To increase the difficulty, bring Category into the view.

You will notice it may not do as you expect with any case. With a filter and a Set it keeps the same N regions for all categories.

Also, it maintains the overall Region sorting, it doesn’t sort the Region within the Category.

This is because the filter on [Region] restricts the data to only include those regions that are in the top N in total.

Using Index it keeps the top N records; in this case 1 region showing the 5 categories.

To show the top N categories per Region is actually a nested top N.

How to Show a nested top N in Tableau

Continuing with the same example, show the top 5 Regions per Category.

To keep the top 5 regions per category requires a different approach.

Table calculations are the solution. Again either Index, First or Rank.

Filtering doesn’t work for a nested top N, a calculated field is the solution.

A normal filter or set for Top N won’t work. As ascertained a normal filter will return the Top N regions across the total data set. Here we want the top 5 per category, not across the data set.

Create a calculated field for the nested top N

Begin by placing the Index field (created above) on Rows. Make it discrete, turning it blue, and place it between Category and Region. This helps set the correct Compute Using settings.

Now it will appear like a count of rows, from 1 to the number of rows displayed.

We want it to show 1 to N descending per Category. Each category will contain numbers 1 to N with 1 being the largest SUM(Metric) and N the smallest. This is the more complicated part.

Right click on the pill and Edit Table Calculation. Compute Using Specific Dimensions.

• Make sure Category and Region are checked
• Category has to be on top to restart the numbering per Category
• “At the level” Deepest
• “Restarting Every” Category
• Sort Order: Custom – Metric – Sum – Descending

That should give the correct numbers for the regions per category.

To only show the Top N, ctrl-click drag that field from Rows to Filters (basically duplicating it) and, as it’s discrete, select 1,2,3,4 & 5.

Alternatively, ctrl-click drag from Rows to Filters then convert to Continuous (green). Then set it to have a maximum of 5. This conversion to continuous helps when choosing a larger number of Top N.

The important thing is the Index calculation in the Filters has the exact same Compute Using as in Rows. Note, the field doesn’t need to be on Rows but it helps when developing.

Now you’ll correctly have the top 5 regions per category.

Both the RANK() and FIRST() functions work in place of INDEX() – in this case they do the same thing. In the below example, showing the Top and Bottom N in the same chart, use FIRST for the Top N.

If developing this for a live dashboard, remove/hide the header of the Index field.

Use a parameter to show the nested Top N

To enable the user to choose the Top N value, change the filter field and instead refer to the parameter (created at the beginning of this article). Create a simple boolean calculated field using Index and the parameter.

If the Index value is less than or equal to the value of the parameter, show the record.

Put the calculated field on the filter shelf. Next, set up the Index calculation within this calculated field. The settings should be identical to those above.

Finally, set the filter to True.

How to show the bottom N records

Showing the bottom N records is very similar to the techniques described above. Depending on the specifics of the task, use either a filter, set or table calculation.

If using a Filter or Set, instead of selecting Top, choose Bottom.

An Index would work for the bottom n, however, there’s a simpler table calculation. Use LAST instead of INDEX. The LAST function numbers the records from the bottom to the top. The last record will have a LAST value of 0, the 2nd last will have the value 1, the next 2, etc.

To show the bottom N first create the calculation:

[Last]: LAST()

Take this field to the filter shelf and, to keep the bottom 5, select all records with a max of 4.

Use a parameter for the user to choose the bottom N

Using the same parameter created at the beginning of the article, to enable the user to choose the value of the bottom N, create a calculated field. The calculation is boolean and compares the value of Last and the parameter.

As the LAST() calculation begins at 0, this time return only values less than the parameter value. (Index starts at one, hence using equal to as well.)

Place this calculation on the filter shelf and select True.

How to show the top AND bottom N

To keep only the top 5 AND bottom 5 use table calculations. Earlier in the post I mentioned FIRST as a table calculation option to keep the top N. FIRST works the same way as LAST, just in reverse. Using FIRST, the first record in the data has the value 0, the 2nd record has the value -1, the 3rd has the value -2, etc. FIRST counts down from zero.

Combining FIRST and LAST in a calculated field means we can keep the top and bottom n records.

The only difference this time, we’ll hard code the top 5 in a calculated field. A parameter could replace the 5. In fact using parameters enables the user to select the top N and the bottom Y records.

For this simple example the top and bottom 5 is sufficient.

Create a calculated field:

[FilterTopAndBottom5]: LAST() < 5 OR FIRST() > -5

Put this field to the filter shelf and set it to True.

To allow the user to replace 5 with something else, replace 5 in the formula above with the parameter created earlier.

[FilterTopAndBottom5]: LAST() < [Top N Parameter] OR FIRST() > -[Top N Parameter]

To finish I have published a workbook to Tableau Public with all of the above examples. You are able to download and better understand how it works.