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

Here’s a question I got from a student named Summer in a DII: Intermediate course in Hong Kong:

“I want to show all of the cities in the United Kingdom for a particular measure but only show city name labels for the top five.”

Awesome question! Let’s get to solving.

We’ll use the Global Superstore data source for our sample data, which is a publicly offered from Tableau and can be downloaded here.

Preparing Our View

I’m going to start with a symbol map of the United Kingdom with my level of detail down to City. I’ll color the circles using Profit and size them using Sales. Finally, I’ll add some stylistic touches using my map layers.

This will be our starting visualization:

I want to label the top whatever cities on the basis of sales. If I do a top N filter on cities, it will filter all of the data for the cities outside of the top N. If I create a set, it will also filter. So, how do I do this?

Table Calcs to the Rescue!

We are going to use a calculated field with a customized table calc inside of it. We’ll start with a logical test for whether the city falls within the United Kingdom. For those that do, we’ll use Rank to determine the order of cities by Sum of Sales, and then we’ll create a special label to include the rank.

Here’s what our calculated field looks like:

Remember, we’re adding aggregation to Country and to City in our calculated field so that the aggregation level matches our measures. You cannot have aggregated and non-aggregated fields in the same calculation.

Now let’s drag our calculation onto the Label button. This is what we get:

Houston, we have a problem.

The reason that we have a bunch of #1’s and not a top five is because the scope in our table calculation is defaulting to Table (Across). That doesn’t help us here. We’ll need to edit our table calculation in our Top 5 Cities calculated field. Tableau Desktop 10 has a new modal table calculation that allows us to see our scope and direction live as we make changes. Pretty cool stuff, eh?

Rather than Table (Across), we’ll need to choose specific dimensions like this:

We’ll also have to modify our Labels options to allow our labels to overlap marks, otherwise we won’t get to see all of them:

And when we do that, we get the following view:

Our third ranked and fourth-ranked cities happen to be right on top of each other, but I promise they are there. To help our report user see them, we can add another new feature from Tableau Desktop 10. Let’s use the Highlighter for our labels. To activate, just right click on the Top 5 Cities field on the Labels button and select Show Highlighter.

We’ll then highlight our #3 city to make it easier to see:

Parameterize the Top N

Let’s take our example another step forward and add a parameter so that our user can decide how many top cities they want to include on our map. I’ll start by creating a new parameter. For my seven-part Deep Dive on Parameters, click here.

Here’s our Top N parameter:

Now we need to edit our Top 5 Cities calculated field to use our parameter (and we’ll update the name, too):

And finally, we’ll show our parameter control. This will allow our user the option of changing how many top cities they want to see, such as say the top 10:

Remember, as we add more labels, they can potentially overlap each other and the marks on the map. Use that Highlighter to help isolate the labels and avoid the clutter.

Thanks for a great question, Summer!