Using Tableau’s INDEX() Function for Easier Top N Filtering

Make filter results more predictable by basing them on row number

Do your filters ever return a result you weren’t expecting? This video shares a simple trick for simplifying your top or bottom N filtering and it even works as you add additional filters to the Filters shelf.

Hi. This is Ryan with Playfair Data TV, and in this video, I’m going to share a trick that will help you do easier filtering in Tableau using a special table calculation called INDEX. To help illustrate, I’ve got this sorted bar chart built with the Sample – Superstore data set. It just looks at Sales by Customer Name. And I’ve thrown on a couple of filters, I’ve said that each customer had to have at least $1,000 in sales, and they had to come from the East region.

Now, let’s say that as a business owner, we want to recognize our top 10 customers. So we want to isolate them, maybe reach out to them, thank them for being such a great customer, maybe offer them a discount. My first instinct, if I wanted to have this filter to my Top 10, is to drag the Customer Name dimension to the Filters Shelf, and there is a tab called Top. You can learn more about these different types of filters in the video “Four Types of Filters in Tableau” if you want to learn more about this.

But on the Top tab, I can set a Top N or Bottom N; N stands for Number. We’ll just leave it as the default for now, so Top 10 by Sum of Sales. And I’m going to click OK. But we’re left with just four customer names on the view.

What’s happening and what’s important to remember is, by default, filters act as AND statements. So we have three criteria at the moment. We’re saying that the Sales has to be at least $1,000, the region has to be East, and the Customer Name has to be in the Top 10. But that’s Top 10 for the file overall. It’s not Top 10 for customers with at least $1,000 and the East region.

We could add the East filter to context, but that’s a topic for a different video someday. What I’m going to show you now is an easier, more predictable way to keep the Top 10 on the view. It involves a calculated field and a very simple formula. I’ll call it INDEX is less than or equal to 10.

And that INDEX is a special type of table calculation, which you could think of as being synonymous with row number. So when it’s going vertically, the Index of less than or equal to 10 would keep your Top 10 rows or your first 10 rows.

And this actually, is the entire formula, same thing I titled the calculated field. The word INDEX, open parentheses, close parentheses, less than or equal to– whatever N I’m trying to keep on the view, in this case 10. You could even take this a step further and use a parameter to determine how many rows you’re keeping on the view, but just to illustrate how this works, there is the entire formula; INDEX is less than or equal to 10. I’m going to click OK.

Now instead of the Customer Name filter– so I’ll drag that away for the moment to get all of our dimension members back– I’ll put INDEX is less than or equal to 10 on the view and let go. This is Boolean. The way that we wrote the formula only has two outcomes. It’s either true or false.

We want to keep only when that statement is true. I’m going to click OK. And now instead of limiting it to just those four names that were across all three criteria, we’re keeping all the Top 10. Because instead of filtering it as Top 10 for the entire file, we’re actually looking at the row numbers. And because these are sorted in descending order, these 10 customers represent my Top 10.

There’s a couple of other advantages to this. As I click around to different regions, it will continue to keep the Top 10 based on those selections. And also, it’s really easy if you’d prefer to focus on the Bottom N, all you’d have to do is flip the sort order. Instead of sorting this in descending order, which would be my Top 10 customers, if I flip this to be sorted in ascending order, it will flip the customer names so that the worst performing customers are on top and my best performing customers are on bottom.

So I very quickly just converted this into a Bottom N instead. But it’s an easier way to get some more predictable results with your filtering using that special INDEX function in Tableau.

This has been Ryan with Playfair Data TV – thanks for watching!