Hello everyone

In Tableau, we can do Top N in the filter to view the Top N items (e.g. Categories) based on the measure we select (e.g. sum of sales). What we will get is the Top N items across the entire dataset. But what if we want to see the top 3 items for each category? We might have thought about changing the Order of Operation, but playing around context filter doesn’t help solve the problem.

Let’s take a look at the Sample Superstore dataset as an example, and we start with Category, Sub-category and sum of sales:

We want the Top 3 Sub-Categories for each Category and we use Top N, we might be stuck with something like below.

And we wanted this:

So, how can we do this? We can do this by using quick table calculation.

Step 1: Right click on “SUM(Sales)” from the Columns, go to “Quick Table Calculation” and select “Rank”. Now we have rank of each Sub-category across the entire table in the view.

Step 2: To change the rank of each Sub-category within each Category, right click on “SUM(Sales)” from the Columns, go to “Compute Using” and select “Pane(down)”. Now we have the rankings for each Category.

Step 3: Drag the “SUM(Sales)” pill into Filters and choose the range we want, in this case, rank 1 to 3.

Step 4: Lastly, we can simply drag Sales into the Columns to view the sum of sales data.

Here we have the Top 3 Sub-categories in each Category in terms of sum of sales.

 

 

The Data School
Author: The Data School