4 Methods to perform Top N based on a condition
The basic Top N filter can be easily computed with Top N Set, Rank, Top N Filter, and Index. How can you compute Top N based on a given condition? So, in this blog, I want to share 4 different methods for performing a Top N filter in Tableau based on a condition.
Note: I have only used Top N Set, Top N filter, and RANK() in the example workbook. Also, all the Sheets will look the same but performed with different methods, So please download the workbook to understand much better.
The workbook is available on Tableau Public here: Top N by conditions
The dataset I am using is Sample Superstore.
Method 1: Top N (By field and formula)
The very basic is to create a Conditional Calculation and then use it in the Top N Filter or Top N Set. Here the condition would be to display Top 10 Customers by Sales with Profit >0 (No Loss acceptable). I have taken 10 as the number but you can create a Parameter to make it dynamic.
I create a Calculation Sales Condition as shown.
After that, use this with Top N Filter as shown below. You can also use it with Top N Set.
For the formula method, We will write the same conditional Calculation in the Formula pane as shown below and click Ok. You can do the same with Top N Set.
If I had to look at Top 10 Customers by Region, and add Region to the filter, Top N won’t work as this gives rise to the Order of Operations problem. The Region is a dimensional filter that is computed after Top N as shown below. So, we will make it a Context filter so that it computes first before Top N.
Method 2: Filter (By field and formula)
This method involves the Customer field. It will work for Customer and Customer Set both. Drag Customer to filter. Then go to Condition and make changes as shown below.
We can write the calculation in the formula pane as shown below if you want to use Formula.
For both examples, I have used RANK() for the ordering of the Top 10.
Method 3: Using Boolean Conditional Calculations
The next method is to use Boolean Calculation with a condition. Create a Boolean Calculation named Profit TF* as shown.
Add this calculation to the filter and set it to True.
I have used RANK() for the ordering of the Top 10. Feel free to use it with the Top N filter and even Top N Set, but remember you will have to use Sales as the Measure in the By Fields as Profit Condition is there in the filter set to True.
Method 4: Rank by Condition
We can use a condition with RANK(). Create a calculation Rank condn as shown below.
Drag this to the filter and compute it using Customer Name. Set the number as 10. This will look as shown below.
Hope you found this helpful. Thanks
Vignesh Suresh
