Tableau: Why is my Top N Filter Not Working?
A quick primer on Tableau order of operations.
A “top N” filter in Tableau is a useful way to see the top number of a dimension. For instance (using the superstore data), maybe you have a list of products displayed by sum of sales. However, there are 1850 products! So perhaps you only want to see the top ten products by sum of sales.
Easy! Simply add the dimension Product Name to rows and the measure Sales to columns. Then, right-click on Product Name and create a filter: Top, By field Top 10 by Sales, Sum:
Now you have the top 10 products by sales displayed.
However, what if you want to break this down further and only show the top 10 products by sales from 2018? Seems easy enough, right? Just add Sale Date to the filters, and select 2018.
WAIT! This doesn’t look right, does it? Why are only nine products shown?
This is a great example of how Tableau’s order of operations works.
How Tableau’s Order of Operations Works
Tableau processes filters in a specific order. It starts with any extract and data source filters, which would have been set up on the data source tab. We’ll skip those on this lesson, and assume you did not set any up.
Next, context filters, which we’ll get to in a minute.
After context filters, Tableau processes any sets, conditional filters, fixed level of detail (LOD) and… our friend, top N.
Then, it gets to any dimension filters.
In our case, we have a top N filter (top 10 products by sum of sales) and a dimension filter (year 2018). Per the order of operations, Tableau hits the top 10 filter first. With this, it separates out the top 10 products by sales out of all products, all years.
Next, it hits the dimension filter, selecting 2018 as the year. The reason there are only nine in the list is that one of the products in the top 10 of all time didn’t have any sales that year.
So how do we fix this so we see the actual top 10 products from 2018?
Back to context filters. As we see in the order of operations, context filters are processed before top N filters. To convert a filter to a context filter, simply right-click on it and select Add to Context. In this case, we’d convert the Sold Date filter to a context filter. This effectively places it above the top N filter in the order of operations. Therefore, Tableau filters the Sold Date to 2018 first, then filters the top 10 of the products sold in 2018.
Now we have the top 10 products by sum of sales in 2018 as expected! And hopefully you have a better understanding of Tableau’s order of operations.
More Resources
Get Your Tableau Operations In Order — this is an excellent lecture/demo on the order of operations from Tableau Conference 2019.
Tableau’s Order of Operations — official documentation from Tableau
