A top N analysis is one of the most widely spread methods to analyze your customers, sales persons’ performance, the most profitable regions. With Tableau you can easily produce this kind of reports or dashboards. Moreover, you can let your users extend the Top 5 list to Top 10 and the most exciting thing is that you can filter your customers based on their profit and show their sales numbers and vice versa! Sounds great, doesn’t it? Let me show how you should proceed.
1. Create your TOP N list
- Start by bringing your customers names on the Row shelf
- Add the sum of Sales to your view by double clicking on Sales in the Measures list
- Bring the CustomerName into the Filter Pane
- Go to the Top Tab
- Select By Field
- Replace 10 by Create a New Parameter
- Choose the Name of your Parameter
- Select your Maximum Value
- Choose your step value (in our case 1)
As you can see the Parameter control will be automatically added to your view. You can now easily move from Top 10 to Top 5 customers list by changing your parameter value.
Nevertheless, you can see that our list isn’t perfect. Let do some extra changes:
- Sort the sales numbers
- Add INDEX() on a row shelf
- Transform INDEX into discrete measure
- Bring INDEX before Customer Name
- Adjust the column’s width
- Hide Fields Labels for Rows
Whereas our list is correct, in a real life we are much more specific. We don’t want to have a Top N list based on all the data that we have in our database, we need a Top N list for a specific year or month. That’s why we need to bring an extra filter to our view, the Order Date. As you can see surprisingly our list moved from 10 members to 8! This is precisely a trap I was speaking about in the title of this post.
2. Context filters
Context filters are really great, whereas they improve the performances of your calculations, they explicitly tell to Tableau the order to use while filtering. As you probably know, by default all the filters are treated independently by Tableau. Adding a filter to a context creates a temporary table, it means that your context filter is applied before the others. Let me illustrate it through our example.
As you can see the filter becomes grey, what indicates to us, that it is a context filter and the most important part is that we have our Top 10 customers again!
3. Calculated field
Another way of dealing with our problem is through creating a calculated field. Knowing that we are interested in the ranking based on the last year (2013 in our case), when can proceed as follows
By the way, it goes without saying that you can replace 2013 by a Parameter allowing your user to select year of ranking.
Even though these two methods work well, I personally prefer the second one as it’s more flexible: you can calculate Year over Year growth, bring to your view their previous years’ sales numbers and so on. Eager to find out what kind of solution you use!