How to Show Top 10 Lists in Tableau Tooltips

How to add Viz in Tooltip in Tableau

To add this sheet as a tooltip in the map, I must navigate back to the first sheet, click the Tooltip Marks Card, click the Insert button in the top-right corner of the tooltip dialog box that appears, hover over sheet, and choose the List sheet.

I’ll also clean up the tooltip by placing the list under the other fields, adding some text to describe what’s in the list, and increasing the max width and height of the sheet in tooltip to 600 to ensure the list has room to be shown.

All the components are in place to display the top 10 list when I hover over an individual state, but look what happens when I hover over the state of California:

Understanding Tableau’s filter order of operations

When you add a sheet to a tooltip as we’ve done, Tableau automatically creates a set filter to the underlying sheet. This is similar to what happens when you create a dashboard action. Here are the filters on the List sheet:

What’s happening due to the filter order of operations is that the list is being filtered to the top 10 products overall first, then we see the products within the overall top 10 for each state that is hovered over. We see four products when hovering over California because four of the top 10 overall were sold in the state of California at some point. If the product was never sold in a state, we wouldn’t see any list when hovering over that state.

New to Playfair Data?

Watch our 90-second intro video and receive tips on making your data usable.
 
Watch Video

The quickest way to get the result we are looking for is to add the newly added set filter to context so the list filters to each state first, followed by the top 10 filter for that state second. To add a filter to context so that it moves up in the order of operations, click on the filter on the Filters Shelf and choose “Add to Context”. The filter will turn gray to show you it has been added to context:

Now when I hover over the state of California, or any other state, the list is filtered to the state first, then the top 10 for that state is displayed in the tooltip.

There’s a slightly more clever solution that will give you more flexibility to show the top or bottom 10 that involves using the INDEX() function for top N filtering.

First, set up a calculated field with the formula INDEX() <= 10 (or whatever number you want to use as the top or bottom N).

INDEX() is a table calculation that returns the row number, so this is a Boolean formula that will only keep the first 10 rows on the view. I’ll replace the Product Name filter with this newly created calculated field and choose True (keeping only the first 10 rows). With this second technique, you no longer need the set filter added to context, so I’ll also remove that filter from context by clicking on the gray filter and choosing “Remove from Context”.

Note I am only having to remove the filter from context because I’m building on my first example. If you opted to start with this second approach, the second filter would have never been added to context to begin with.

This approach will always keep the first 10 rows displayed regardless of which state is hovered over. The only catch with this approach is you have to ensure the list is sorted appropriately. If you want to show the top 10 products in this case, we would need to make sure the list was sorted in descending order. What’s nice about this is we can easily display the bottom 10 by simply flipping the sort order from descending to ascending. This puts the product name with the lowest profit value first, and our Boolean filter keeps the 10 worst performers on the view.

In our map view, Texas is interesting because it appears to be the state that we lost the most profit in. Now if I hover over Texas, the top 10 list will be shown; only now it’s been toggled to display our bottom 10 products by profit (I’ve also changed the list title accordingly).

Note that throughout these examples I’ve used top 10 and bottom 10, but of course these can be set up to show whatever top N or bottom N is appropriate for your business.

Showing both top N and bottom N

I’ll close this post with one last idea. You can set up two separate sheets – one sorted in descending order to keep the top N and one sorted in ascending order to keep the bottom N – and add them both to the tooltip.

But you can also consolidate the two sheets into one by modifying your INDEX() <= 10 formula to the following:

INDEX() <= 10 OR LAST() < 10

LAST() is another table calculation that computes the difference between the current row and the last row. A difference of less than 10 would represent the last 10 rows. The difference between the last row and itself is 0, the second to last row is a difference of 1, and the difference with the tenth to last row is 9. When your table is sorted in descending order, this formula would keep both the top 10 and bottom 10 on the same table!

Thanks for reading,
– Ryan