How to Show Top N in Tableau

Displaying the top or bottom values in your data is a common task in Tableau. The Top N feature allows you to quickly filter your views to the most or least significant rows.

In this article, you'll learn multiple methods for showing the top and bottom records in Tableau Desktop. The material from this article is part of our tableau course. JBI Training can supply training in Tableau to your entire team. 

Introduction to Top N in Tableau

Analysing the top and bottom performers in your data is crucial for many business objectives. For example:

• Identifying top-selling products to promote or replicate success.
• Spotting high-value customers for rewards or engagement.
• Finding the least profitable products to cut or improve.

Tableau has built-in functionality for easily filtering your views to the Top or Bottom N records according to a measure. You can also use table calculations or level of detail (LOD) expressions to compute a rank column and display the top values.

We'll cover the pros and cons of each approach. Let's get started!

Using the Built-In Top N Filter

The easiest way to filter for top values is by using Tableau's Top N filter on the Filters shelf:

1. Drag the dimension you want to filter to the Filters shelf.
2. Right click on the pill and select "Top..." to open the filter menu.
3. Choose if you want the Top or Bottom values.
4. Enter the number of rows to display.
5. Select the measure to rank by.

For example, to show the 10 customers with the highest sales:

1. Drag Customer to Filters.
2. Select "Top..."
3. Choose Top, 10 rows, and Sales.

This will dynamically filter the view to only the top 10 customers by sales each time you open the workbook.

Customizing the Top N Filter

You can customize the filter to change:

• Sort order - Switch between descending and ascending.
• Calculation - Rank by measure instead of sum.
• Tied values - Keep or discard tied values at the threshold.

For example, you may want to use a custom rank measure that handles ties, rather than the default sum.

Using Table Calculations for Top N

Table calculations offer more flexibility for computing a rank column to find the top values. You can then filter on the new rank field.

Common ranking table calculations:

• RANK - Ranks values from 1 to N, leaving gaps for ties.
• RANK_DENSE - Ranks values from 1 to N without gaps.
• RANK_UNIQUE - Ranks unique values from 1 to N with gaps.

Steps to filter top N with table calcs:

1. Create a calculated field using a rank function like RANK([Sales])
2. Place the rank field on Rows or Columns shelf.
3. Right click on the rank pill and filter top or bottom values.

For example, to find the top 10 states by sales with ties:

// Table Calculation 
RANK_DENSE(SUM([Sales]))

Table calculations offer more ranking options compared to the built-in filter. However, they can impact performance as the calculations occur dynamically on extract refresh.

Using LOD Expressions for Top N

When you need to rank large datasets, level of detail (LOD) expressions can optimize performance.

LODs fix dimensional values so calculations run at the aggregate level. Steps:

1. Use FIXED to determine your partition.
2. Use RANK functions to calculate rank.
3. Filter top values with TOPN or BOTTOMN.

For example, to find the top 10 categories by sales:

// LOD Expression  
{FIXED [Category]: TOPN(10, SUM([Sales]), [Category])} 

LODs allow ranking calculations to be computed during extract refresh rather than live. This boosts speed with large or complex data.

Comparing Top N Methods in Tableau

Method	Pros	Cons
Top N Filter	Simple setup	Limited options for rank measure and ties
Table Calculations	More ranking flexibility	Can cause slow extract refreshes
LOD Expressions	Fast ranking of large data	Requires specific LOD syntax

Top N filter - Great for quick analysis with the default sum.
Table calcs - Allow custom rank measures and tie handling.
LODs - Optimize ranking performance on large datasets.

Choose the technique that meets your specific needs.

Visualising the Top Values

Once you've filtered to the top records, effective visualization can draw attention to the top results.

Bar charts are a great way to showcase top values using bar length. Sort bars descending to emphasize the top items.

Highlighting like colour or reference lines can call out top results clearly.

Labels displaying ranks or record counts make it easy to identify top and bottom values at a glance.

Conclusion

Finding the most and least significant values in your data is key for many business analytics use cases. Tableau provides several options for filtering to the Top N records:

• Top N filter - Quickly filter top/bottom values.
• Table calculations - Rank with custom measures and tie handling.
• LOD expressions - Optimize top N performance.

Use the right method for your specific needs. Effective visualization brings out the top results.

You now have the skills to analyze and showcase your top-value data in Tableau!

Frequently Asked Questions

How do I show top 5 instead of top 10?

In any method, simply change the number of rows to display. For example, set the Top N Filter to 5 rows or use TOPN(5) in a LOD expression.

What if I have multiple records tied at rank 10?

The built-in filter will include all tied records. For precise control, use table calcs or LODs to configure the exact tie behavior you want.

Can I create a percent of total rank measure?

Absolutely! Write a custom table calculation using the SUM() and WINDOW_SUM() functions to calculate percentage of total.

This article is part of our ongoing series of how-to articles related to Tableau. You might also enjoy how to concatenate in tableau or our article or how to create top n parameter in tableau