Our Certified Tableau Trainers are asked a myriad of questions each day. In this series, they share some answers.

While teaching a Desktop I and II Accelerated class last week in Perth, I got an interesting question from a Tableau student named Clint.

“Can you make a highlight dynamic so that it will highlight just the top 10 or top 20?”

When it comes to Tableau, the answer is always a big YES! For this particular solution, we’ll use a combination of mark cards, parameters and calculated fields. Let’s build it!

Start with a Crosstab

I’ll start by using the publicly accessible Global Superstore data set from Tableau. We’ll begin our exercise with the following crosstab:

Arrange Market on Columns with Category and Sub-Category on Rows. We’ll add Sales onto the Text button, formatted as Currency Custom without any decimals.

Build the Parameter

We’ll add a parameter to our crosstab to enable our user to pick how much of the table they want to highlight. I spent a lot of time on this topic in my Deep Dive on Parameters series. Read through the posts if you need a refresher on how parameters work. Here’s how we’ll build our parameter control for our Top N:

This will allow our user to see from the top 1 to the top 20 highlighted in the crosstab. This functionality closely mirrors the flexibility you have in Excel to arbitrarily show the top N in tables. Within Tableau, which is more programmatic in its formatting, we’re relying on our parameter above to help.

Calculated Fields

Our range of values is pretty wide. To help normalize these colors and make them more distinct across our Top N, let’s use the Rank table calculation. We’ll need two different calculations. The first is for our color gradient. Here’s the calculation:

Our top value (i.e. the first rank) will have an assigned value of 20 on our color gradient as a result of the calculation above. We need to take our 1, 2, 3, etc. rank and arrange them from high to low. If you want to show more than a top 20 in your parameter, simply take your highest range increment and type +1 for this calculation instead of 21.

All other values outside the Top N selection are assigned a value of zero. The second calculation is for our rank label to be placed alongside our sales value. Here’s the calculation:

Our goal here is to add a rank to our highlighted cells. If the cell is not within the Top N, then we ignore it for this calculation.

Marks Card: Color Button

Now, let’s add our calculated fields to our Marks card and do some tricks on our color legend. First, add the Sales Rank (SUM) Color calculated field to the Color button. We’ll need to ensure that the Rank table calculation is computing using specific dimensions. I want a rank across the entire table for all fields, like so:

For the color gradient, I want one end of the spectrum to be a color and the other end to be white. At the bottom of the color palette drop-down box, there is an option for Custom Diverging colors. I can’t use Custom Sequential, because it will default one end of my spectrum to light grey. I need to specify white, so I’ll choose red to highlight my Top N. Here are our colors:

I’ve expanded the Advanced options to show how our calculated field will interact. Our #1 rank will always be the value of 20 at the top of the spectrum. Our bottom end normalizes all other numbers outside the Top N as 0. It creates a concise gradient where there are discernible differences between our values in the highlight table.

If we don’t do this trick on the color palette, the range of numbers could be so diverse that the top ten ranks could basically be the same color in our view, which will only highlight the top numbers rather than indicate their order. This gives us the following view so far:

Marks Card: Label Button

We could stop here, but I want to add a little extra notation for the rank on our Top N to make it easier to discern which shade of red is higher. I’m going to use our second calculated field and drop that onto the label button and format it to look like a subscript, like this:

Even though the font size drop-down stops at eight, you can manually enter in a smaller number. I used six to really differentiate the size of the sales text from the rank number.

Let’s add a finishing touch for a sub-title to have some context for our Top N filter. Here’s how our table is published on Tableau Public. And don’t forget to show that parameter control!

And we are done-zo! Thanks, Clint!