How to Automatically Highlight Highs and Lows in Tableau

Two ideas for bringing attention to minimum and maximum values

Make Tableau answer business questions for you automatically using a calculated field to automatically color specific cells in a highlight table. Also see how to improve a line graph by making the highest and lowest numbers stand out.

Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to show you how to highlight the highest value and the lowest value on a highlight table automatically. I’m also going to show you on a sparklines or line graph view how to show a data point for the highest number and the lowest number. This is on the advanced track because what we’re doing here is we’re making Tableau do the work for us. If you can really master some of these concepts, you’re going to really unlock a lot of value in Tableau Desktop.

So a fairly simple example to start with, but what we’re doing is we’re making Tableau do some of the legwork for us, which is going to be really helpful down the road in our analysis. To illustrate what I’m going to show you, I’ve got this text table over here in Tableau Desktop looking at the Sales measure by month of Order Date and Sub-Category. This is a very common exercise for me whenever I’m presenting on this topic. I ask the end users, or my audience, to try to answer a very simple question. I say, what’s the highest number in this data set? And if they get it too fast, I ask them, what’s the lowest number in this data set?

Well, now with Tableau, I’m going to make a calculated field that just answers that for us to make it even easier and even more foolproof. To start this, I’m going to create a calculated field, and we’ll call this Min / Max Highlight. And I’m just going to paste the formula. But I am going to walk you through what this is doing. Tableau never likes when we copy and paste quotation marks. But what this formula is doing is it’s saying, look at each sales value and determine whether or not that equals the ‘window max’.

Window MAX is a table calculation. So it’s going to look at the window of data. And I’m going to show you in a moment how to control how the window is defined. But it’s looking at, essentially, the entire window of data and if the sales value in the cell matches the maximum sales value, we’re gonna call that ‘Max’. Then we’re going to do the same thing on the minimum side. Window MIN is another table calculation. So we’re saying, look at the values for each cell. If the value in that cell matches the minimum sales value, then it’s going to be defined as minimum. Otherwise, it’s ‘Neither’.

I’m going to click OK. And I’m going to drag that highlight to the Color Marks Card. And let’s see. The colors are a little close together. So let me change a couple of these by just remapping these. Instead of, let’s see, for Neither, I’ll make that a gray. And the rest we’ll leave as is. That’s good enough. And click OK. So let’s take a look. See how it did. Looks like there’s quite a few blue values and orange values. But we’re trying to have Tableau answer the simple business question for us, what’s the highest number overall and the lowest number overall?

So obviously, there’s more than one min and more than one max, so not quite what I wanted. What this is doing is it’s running each cell through the logic. And I can see that there’s a delta symbol on that highlight dimension, which tells me a table calculation is taking place. If you think back to the video An Introduction to Table Calculations here at Playfair Data TV, you would know that by default, table calculations are always computed left to right, so ‘table across’. So what we are seeing at the moment is the highest number and the lowest number for each row is what we’re doing.

To fix the logic, what we need to do is change the addressing of that table calculation so it includes not only months moving left to right, but also sub-categories moving top to bottom. You can change the addressing of a table calculation by clicking into it and clicking Edit Table Calculation. Like I mentioned, by default, it’s going table across. But if I choose specific dimensions, I can add Sub-Category to the addressing. Now if I click out of this, we should see one min and one max. And sure enough, that is what we see, blue for max at 57,429, orange for minimum at $88.

The next thing I’m going to do is convert this into a highlight table and show you a little hack to get the cells to line up properly. To convert this to a highlight table, I’m going to change the mark type from Text to Square. And you can see our color coding is still intact. Doesn’t quite look like a highlight table because we’ve just got one little square in each cell. To make those squares larger, I’ll click on the Size Marks Card and drag this to the right.

But now notice what’s happening. It looks like we’ve got orange in multiple cells, my blue went away, there’s something white here. I– and you can even see when I’m scrolling over, these squares are huge. And what’s happening is they’re spilling into other cells. And they’re overlapping with each other. I’ve got a little hack to correct this for you. All you have to do is double click on the Columns Shelf and type two quotation marks, so single tick mark, single tick mark, Enter. You can already see that it’s starting to realign from left to right. It already looks a little bit better.

But we need to do the same thing on the Rows Shelf. So I’ll double click on the Rows Shelf, single tick mark, single tick mark, Enter. And now we can see we’ve got this nice looking highlight table with a colored in square in the background. The only real side effect to using those quotation marks is they become part of the header. But I typically hide those anyway, which you can do by right-clicking and clicking Hide Field Label for Columns. Do the same thing on rows just to clean that up a little bit.

So that’s the first example of making Tableau do some of the work for us. We just made Tableau answer a business question for us. We’re able to figure out what’s the highest number and the lowest number with extreme efficiency and extreme accuracy. So excellent example, I love to, any opportunity I can get, to make Tableau do the work for me.

The second example I’m going to share with you is a way to make Tableau, yes, do some of the work for us. But it’s also a little design tip. I think it looks a little bit nicer. What we’re going to do is create a dual-axis combination chart where we’ve got a line graph on one axis and a dot plot on the right axis. But the only dots that we’re going to show is if the dot is the highest value or the lowest value for that row.

So to do this one, I’m going to start with a line graph that looks at Sales by continuous Month. And you could break this down by whatever you want. Just to give you an example, I’ll pull Region on to rows so that we’ve got four rows of data. And to create this, I’m actually just going to edit our Min / Max Highlight a little bit. And I’ll call it now Show Data Point.

And the formula is just a little bit different. We’re going to say, IF the SUM of Sales equals the WINDOW_MAX, or the SUM of Sales– I’ll put that on a second line so we can see it a little bit better– or the SUM of Sales equals the MIN, this time what I want Tableau to do is show that sales value. And because we’re using different data types, I’m getting an error message for this ELSE ‘Neither’. So I’m just going to delete that part of the code.

So there is my whole formula. Just one more reminder on what this is doing for us. It’s going to look by default across each row and determine for each data point, is it the highest or the lowest sales value? When it is, it’s going to show the sales value. Otherwise, it’s going to come up as null. So it just won’t show anything. Going to click OK.

By default, this Show Data Point calculation, notice– and I think this is because we converted it from a String data type to a measure– but it has a– it’s got a blue icon next to it, which tells me it’s discrete. So if you’re in a similar situation, you might see this green by default. But if it’s blue, we need to make sure that this calculated field is used as a continuous field. We can easily change that by right-clicking on the field and clicking Convert to Continuous. Notice the icon went from blue to green.

I’m now ready to make this into my dual-axis combination chart. I’m going to left-click on my Show Data Point measure. And drag it to the right side of this chart. That dashed line is telling me where it’s going to draw this axis. If you’re not familiar with this chart type, I encourage you to check out the video here at Playfair Data TV called 3 Creative Ways to Use Dual-Axis Combination Charts. It’s a very powerful technical feature that unlocks a lot of flexibility, including the example I’m about to show you.

So if I let go, you see we’ve now got two colors, the blue line or original sales values, and then we’ve got something called Show Data Point. Also because we’ve got two measures on the Rows Shelf, they each get their own set of Marks Cards that can be edited independently of each other. So I’ll navigate to the Marks Cards for the Show Data Point measure and make a couple of changes.

First I’m going to change the mark type from Line to Circle. So we can see those a little bit better. When I change the mark type, I can see that these axes are not quite in sync. In order to synchronize the axes, you can right-click on either side of the dual-axis chart and click Synchronize Axis. When I do that, we now see the circles line up with the appropriate measure value on the line. I’ll make these even a little bit bigger so we can see it.

But in this example, we’re going from left to right. Remember, that’s how our highlight table defaulted as well. In this case, it’s exactly what I want because I want some extra distinction on the highest number as well as the lowest number. And sure enough, that’s what we see here. We see two circles per row. If the value in the line graph matches the highest value for that respective row, a circle’s drawn. Same thing on the low end. If the value for the line graph matches the lowest value for that region, we see a circle appear.

So it’s a nice way to call attention to the high points and the low points. This was just an introduction. You could take this further. There’s lots of applications of this. You could use that same calculated field to show labels or something different. You could also combine the first calculated field that we made with the second one to color those circles. So not only show them, but you might want to color the high one blue and the low one red, things like that.

But in both of these examples, I showed you how to automatically highlight the highest numbers and the lowest numbers. One benefit to this is we’re making Tableau do some of the work for us. Another benefit to it is it just gives a little bit of professional polish. It improves the user experience and calls attention to things that need to be looked at in the business.

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