One Line of Code to Get Both Top N & Bottom N

There seem quite a few other posts regarding how to get top N and bottom N. It is a recurring topic. Here is an example:

How to create Top 5 and Bottom 5 values with respect to a Measure in Bar graph in same worksheet

Just figured out a simple way of dealing with this class of questions.

Top 5 and Bottom 5

For top 5 and bottom 5, this line of code as filter will suffice: (Use Rank() if you wish)

• Rank_Unique(Sum([Sales]))<=5 or Rank_Unique(-Sum([Sales])<=5

or

• Rank_Unique(Sum([Sales]))<=5 or Rank_Unique(Sum([Sales],'asc')<=5

We are using both descending and ascending sorts. That's it. (The first formula may not work in some Tableau desktop versions. Rank_Unique(-Sum(sales)) may not be accepted).

Sort the result to make it look better. But sorting may not be a requirement.

Top N and Bottom N

To generalize a bit, for top N and bottom N, we can do this using a parameter N:

• Rank_Unique(Sum([Sales])<=N or Rank_Unique(-Sum([Sales])<=N

Add Color

Color is nice to differentiate. Use this calculated field to color:

• Rank_Unique(Sum([Sales]))<=5

Add Rank

Create a calc field Rank_Unique:

• Rank_Unique(Sum([Sales]))

Right click it and make it discrete. Drag it to first column to view the ranking.

Voila! Click below to download the workbook.