How to Show Top N Lists in Tableau Tooltips

Blurring the line between explanatory and actionable analytics

Ryan shares one of his favorite tactics for helping users evolve from text tables to data visualizations. You’ll learn how to use the Viz in Tooltip feature and some tricky ways to get around Tableau’s order of operations for filtering.

Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to show you how to show Top N lists within Tableau tooltips. This is one of my favorite tactics to get people to evolve from that spreadsheet mentality and start making that leap to data visualization.

It does use the Viz in Tooltip feature, but the order of operations in Tableau makes it a little bit trickier than it sounds. So to be clear, what I’m going to show you in this video is how to show a visual. And when you hover over a mark, it’s going to show a top 10 list in a tabular form for whatever dimension member you had hovered over.

To illustrate, we’re going to use this map that looks at SUM of Profit by State in the Sample Superstore dataset. And I’m going to create a top N list. Most people think of this as top 10, but this could be top 5, top 20, whatever the case might be.

And let’s say we want to look at Profit and Product Name. So I’ll click on those two, go to Show Me, build a quick table. And I will also sort this in descending order. So we’ve got our top 10 or all of our products actually at this point sorted by their profit values.

The next thing I might do is add a filter on top N. And I’ll just do top 10, which is the default. I’ll drag Product Name to Filter, click Top, and filter this to just show my top 10 products by profit. Click OK. Note in the bottom-left corner of the summary, it says we’ve got 10 marks on the view.

I will now jump back over to my map, and click on the Tooltip Marks Card, and add a Viz in Tooltip by just navigating to where I want within the Tooltip, clicking the Insert button, and hovering over Sheets. These are all the sheets I have available to me to add as a Viz in Tooltip. Click that.

I might make a couple of changes here. Because this is a vertical table, maybe I’ll bump this height up to 600. But let’s take a look at how this looks. I’m going to click OK. Then I’m going to hover over the state of California. So there is our top 10 list. But when I hover over the state of California, I’m shown only the top four.

Texas is really drawing me to hover over it– if I hover over that because it looks like it’s the most negative. So maybe I want to take a deeper look here. Well, for Texas, it only shows one product. I’m hoping to see the top 10 or top N, whatever number I choose, for each state. But let me jump back here to the Top N List and see if I can get any insight into what’s happening.

So notice we’ve now got two filters on the view. We added the top 10 product filter. But now, we’ve got a second one called Tooltip. Tableau treats the Viz in Tooltips as a Set Filter. That’s why you see that Venn diagram on that pill.

And what’s happening is these are competing with each other in the order of operations. So when we see California has four products, that means that there were four products sold in the state of California that were also in the top 10 overall. It’s not quite what I want.

There’s two ways to fix that. One is we could move this Set Filter up in the order of operations by adding it to context. To do that, you click on the pill and choose ‘Add to Context’. It gets this special gray color-coding.

And we know that that Set Filter is going to happen first in the order of operations. It’s going to filter it just to the state of California if I hover over California. Then it’s going to show me the top 10 filter next. Let me jump back here to Profit by State. And now sure enough, we see the top 10 products in California. If I look at Texas, also see the top 10 there. So that’s option one.

Option two is a little bit more clever. And it’s got a little bit more advantage because we can easily toggle to look at the top 10 or the bottom 10. And that’s to use an index filter. I show you how to do this on a different video. You can make a calculated field out of this. But you could also do this in the flow by double-clicking on the Marks Shelf and typing in the formula.

If I was wanting to show the top 10, the formula would be the function INDEX, open parentheses, close parentheses, less than or equal to 10. INDEX is a special table calculation. I think of it as being synonymous with row number. So if it’s a table calculation, and it is computing from the top of the table to the bottom of the table, this is going to show me any row number that is less than or equal to 10.

Going to click Enter. Whenever you add a field in the flow like this on the Marks Shelf, by default, it gets added to the Detail Marks Card. We’re going to drag that up to the Filters Shelf in just a moment. But first, I’m going to get rid of my original Product Name filter to show you that this is working. So I’m going to drag that away. We’re back to 1,850 rows worth of products.

But now I’m going to drag my index is less than or equal to 10 field to the Filters Shelf. So I’m moving it from the Detail Marks Card to the Filters Shelf. I want to keep only the marks that are True and click OK. And we’re back to the top 10.

If I go look at my Viz in Tooltip– and actually, we no longer need this on context. That’s another advantage. So I can remove my Set Filter from context. If I go back to my map, this is still working for me.

Another advantage to using index instead of the context approach is if I flip the sort order– so right now, it’s going in descending order. If I flip that to ascending order, we’ve just immediately toggled this from the top 10 to the bottom 10.

And again, this is top N or bottom N. You can also parameterize that selection, allow your user to choose from top five or bottom five, top 10, bottom 10, and so on. But we just toggled it to bottom 10. Now, if I go look at my map again and hover over any of these, we’re looking at the 10 worst products.

And I’ll show you one more clever thing. You could also set up a calculated field that shows you both the top 10 and the bottom 10 or the top N and the bottom N. But I’m going to roll with the top 10 example. So we’ll call it top 10 slash bottom 10. The first part of the formula is the same that we started with. INDEX() is less than or equal to 10.

And we’re going to add a little bit more logic this time. We’re going to say OR, we’re going to use another special table calculation function called LAST(). This is going to find the last– it’s going to find the difference between the index or the row number and the last row. So the logic this time would be my LAST() difference is less than 10.

The reason we don’t use an equal sign this time is the very last row, the difference will be zero. So if we’re wanting to get 10 rows, it’s going to be less than 10. If we did less than or equal to 10, we’d actually get 11 rows for the bottom. But there’s the entire formula for top 10 or bottom 10. Going to click OK. I will drag my original index is less than or equal to 10 off of the view.

So once again, we’re back to 1,850 rows. But I’m going to replace that filter with Top 10 or Bottom 10 and choose True. Click OK. And now we have 20 rows. This is still sorted in ascending order. So we’re seeing our bottom 10 followed by our top 10. For this example, I’d probably flip this back to be descending sort order, which is showing my top 10 products by profit first, followed by my bottom 10 products by profit second.

Now, this tooltip’s becoming pretty powerful for me. We’re definitely blurring the line between explanatory metrics and actionable metrics. When we’re just looking at the map in a vacuum, I can see certain things like New York and California had the highest profit values. Texas had the lowest profit values. But I don’t really know how to act on that.

Now, if I hover over, I can see the top 10 products in California, maybe try to create more customers like that, or focus on marketing those products. So I’ve got some actions from that. For Texas, on the other side of things, I can look at the negative profit and see things like the GBC Docubind P400. The Tableau folks that made the sample dataset are pretty clever there. But maybe I want to explore what’s going on. Why is that product so unprofitable?

So just one of my favorite tactics that kind of is blurring the line between explanatory and actionable. It’s helping me move my stakeholders from tabular views to something more visual, but it still provides that raw data in the background.

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