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.