I recently helped with a Tableau community forums question where the user needed to:

• filter to the top N categories based on a measure,
• using a dense rank (so that categories with the same value had the same rank),
• but list no more than M categories within each rank (based on another criteria)

This screenshot illustrates the requirement where we want to see the top 5 categories based on sales, where categories with the same sales have the same rank. But we only want to see the top 2 within each rank, based on lowest cost:

For example Vegatable, Fruit and Cereal are all ranked joint top on sales, however only Vegetable and Fruit should be shown as they are the lowest 2 based on cost.

My first attempt to help was a complex soluton involving PREVIOUS_VALUE (I’m a sucker for this function!) but on reflection I ended up providing an option based on more usual table calculations (RANK_…) and a level of detail expresssion. I’ll go through this option, and why it was necesary, over the rest of this blog post. And I might even share the PREVIOUS_VALUE one in a separate post for those interested!

First up the data set I used to test the solution looked like this:

You’ll note that there is some work to do to aggregate up to the category level.

Filtering to the top-N categories on sales is relatively straightforward. I use RANK_DENSE because I want categories with the same sales to be ranked the same, and to have no gaps in the ranking.

Rank – Sales =

RANK_DENSE(SUM([Sales]),'desc')

I can then drag that onto filters and filter to up to 5:

Things get a bit more complicated next when I want the lowest two on cost within each ranking. Your first thought might be to create another ranking based on cost that is partitioned by (restarts for every) ranking on sales. However when you get into those settings within the table calculation you’ll notice that you can’t use the existing table calculation. “Rank – Sales” is not in the list!

In fact no measures will show up in that partitioning list, yet we need to partition by the sum of sales or the ranking based on sum of sales. This is where the level of detail expression comes in. We can create a sum of sales that can be turned into a dimension.

Category Sales =

{FIXED [Category]: SUM([Sales])}

NB: Once you’ve created this, right click it and convert to dimension. Also if you need other filters on your view you’ll need to remember that the FIXED level of detail expression is busting out of that level of detail (filters) on the view. To get around that you can either use context filters (which apply prior to the level of detail expression being evaluated), or you can add the other dimensions you’re filtering on into the list of dimensions you’re fixing by (before the colon in the calc above).

Now that we have a dimension of category sales we can create another ranking table calculation that is partitioned by that, so that we get a ranking on cost that restarts for each group of categories based on sales.

Rank – Cost =

// Results are computed along Cateogry Sales, Category.
// The calculation restarts at zero for every Category Sales
RANK_UNIQUE(SUM([Cost]),'asc')

And from here we can construct a view that lets us filter to the top N categories on sales, and within each ranking the top M based on cost:

Job done! Do you have a better or simpler approach? If so I’d love to hear about it!