Let's use the classic Superstore example. We want to get Top N Categories per Department in terms of Sales.
Nested Sorting
Step 1.
Drag Department and Category to the Row shelf.
Step2.
Create a calculated field Rank_Unique: Rank_Unique(Sum([Sales])) .
Step 3.
Drag it to the Row shelf and turn it into Discrete. Move it to between Department and Category.
Step 4.
Right click on Rank_Unique and select Compute Using>Category
Now, the Nested Sorting is achieved without [Sales] in view! Yeah. We don't really need it if all we want is the ranking of categories per department.
.png)
Top N Filter
Next is to set up the Top N filter.
Step 5.
Drag the green Continuous pill Rank_Unique to the Filter shelf. Click OK when the filter settings pops up.
Drag the green Continuous pill Rank_Unique to the Filter shelf. Click OK when the filter settings pops up.
Step 6.
Repeat Step 4, for the filter pill. Select At Most for the filter settings when prompted again.
Repeat Step 4, for the filter pill. Select At Most for the filter settings when prompted again.
Step 7.
Right click Rank_Unique and show quick filter. And we have a sliding bar for Top N.
Right click Rank_Unique and show quick filter. And we have a sliding bar for Top N.
Voila, we are done!
An alternative is creating a Rank_Unique<=N filter. I will leave that as an exercise.
An alternative is creating a Rank_Unique<=N filter. I will leave that as an exercise.
Dress Up
The above is the minimum barebone version of nested sorting and top N per category viz. To dress it up, we may need to do the following.
- To see the visuals and values, drag [Sales] to both Column shelf and Label shelf.
- Hide the rankings by unchecking Show Header under Rank_Unique.
- Edit the filter title to be Top N.
- Drag Department to Color Shelf to add some color.
- Drag Department to Color Shelf to add some color.
The workbook can be downloaded here.
Bottom N
Given that we know how to get the top N per category, we only need to replace
Rank_Unique(Sum(Sales))
by
Bottom N
Given that we know how to get the top N per category, we only need to replace
Rank_Unique(Sum(Sales))
by
Rank_Unique(-Sum(Sales))
Following the same steps, we get the bottom N per category. Make sure the table calculation is always Compute Using>Category.
Both Top and Bottom N
Create a filter as follows:
Drag it to the filter shelf and fix the table calculation. Then it's done.
A more detailed post on this can be found here
One Line of Code to Get Both Top N & Bottom N
Following the same steps, we get the bottom N per category. Make sure the table calculation is always Compute Using>Category.
Both Top and Bottom N
Create a filter as follows:
Rank_Unique(Sum(Sales)) <=N or Rank_Unique(-Sum(Sales)) <=N
Drag it to the filter shelf and fix the table calculation. Then it's done.
A more detailed post on this can be found here
One Line of Code to Get Both Top N & Bottom N
Why not Rank()?
Rank() function produces non-distinct rankings as well as Rank_Modified() and Rank_Dense() do. Thus sometimes we may not get exactly N elements using a Rank()<=N filter. Rank_Unique() returns distinct rankings even for identical values. If you expect 10 elements consistently from a Top 10 filter, instead of 9 or 11, Rank_Unique is the way to go.
A review of rank functions in Tableau is here.
A review of rank functions in Tableau is here.
.png)
View comments