How to Make Spark Bars with MIN MAX Indicators in Tableau

Rounded Trend Bars, Custom Color Indicators, and a Labels Trick

You’ll learn how to use the generated Measure Values field and MIN(0) placeholder to round spark bars in Tableau. Ryan also shares multiple techniques for indicating whether values are the highest or lowest and a hack for aligning mark labels.

Calculation that classifies points as “Max”, “Min”, or “Other”:
IF SUM([Sales]) = WINDOW_MAX(SUM([Sales])) THEN “Max”
ELSEIF SUM([Sales]) = WINDOW_MIN(SUM([Sales])) THEN “Min”
ELSE “Other”
END

Hi, this is Ryan with Playfair Plus, and in this video, I’m going to show you how to make rounded spark bars in Tableau. But I’ll also be sharing a dynamic, calculated field that allows you to automatically indicate the highs and lows within that spark bar. I’m also going to show you a trick that helps you align labels and one last trick that allows you to display the highs and lows within the spark bar.

By the end of this video, you’ll be able to make a view that looks similar to what you see on the screen here. We’re looking at Sales by Region in the Sample Superstore data set. You can see that those spark bars are rounded. You can also see that the highs and lows per region are highlighted. And the labels for only the highs and lows are displayed.

To get started over here in Tableau Desktop, I’m just going to start with the foundation, which is that rounded spark bar. For this view, the special aspect of this is that it is built with the generated field called Measure Values.

So I’m going to start by dragging Measure Values to the Rows shelf. And by default, what that’s done so far is shown every measure that comes with the Sample Superstore data set at its default aggregation. And they’re all just stacked on top of each other here.

We are also going to break this down by Region. And before I go any further, I’m going to show you the trick to making these rounded. The trick is we want to connect whatever KPI we’re looking at. In this case, that would be SUM of Sales with the placeholder field that I create all the time that I just call MIN (0).

Well, I haven’t created Min zero yet, so I will add it to the Measure Values shelf by double clicking on that shelf and typing the aggregation MIN, and then just wrap the number 0. So that has added an additional measure to the view, but it just equals 0.

Then I remove every measure that I don’t care about in this case. So I will remove everything except for SUM of Sales and MIN (0). To round these and to connect these two measures so that they appear to be a rounded bar, you change the Mark type from Bar to Line. And then you put Measure Names onto the Path property of the Marks card.

And if I were to increase the size of that line, you’ll see that those actually are rounded. So that is the foundation, and now, just like with any other sparkline or bar, if I’m wanting to show a trend over time, I’m going to break down the Measure Values and Region by some element of time. I will use Order Date.

Now, there’s one more trick with this, is it’s very important, if you like that rounded aesthetic, that you use month or whatever date part you’re using as discrete. If you use them as continuous, Tableau will no longer know how to connect the dots and give you that nice rounded look. So for this view, there’s a slight limitation where I need to choose month, year, and make sure it is discrete.

Because I know what date part I want to use and that I want to use it as discrete, I will use a shortcut, which is to right click on my element of time while I drag it to the Columns shelf. That allows me to choose month, year with the blue calendar icon telling me it will be discrete. When I click OK, we’ve now got spark bars, and I’ll fit the entire view just so we can maximize the space there.

So that is rounded spark bars. One other pitfall I’ll point out because we have another video here at Playfair Plus that shows you how to make traditional sparklines that shows you all the different KPIs in your view. It also uses the Measure Values generated field. It also uses Order Date, but Order Date, in that case, is continuous. And it doesn’t allow us to use the MIN (0) placeholder that I’ve done here.

So in summary, if you want the traditional sparkline view that shows every single metric, I’ll share in the related content the other video on how to make sparklines. This view is a little bit– or this aesthetic can be used whenever you’re looking at a single metric. You can break it down by whatever dimensions you want, but it requires a single metric that we then connect to MIN (0).

But because we’ve got a single metric, SUM of Sales, we’re in good shape. That’s the foundation. I already think that this looks much better than the defaults. This would be a great choice for showing a 10,000 foot view trend, probably along the top of a dashboard with a certain KPI.

But we’re going to make this even better. I promised I’d show you a calculated field to dynamically indicate the highest and lowest values in this view. To do that I’m going to create a calculated field, and I’ll call this my MIN / MAX Categories.

And the formula is IF SUM of Sales equals the WINDOW_MAX SUM of Sales, then that’s the MAX. Next line is ELSEIF. I’m going to do the same thing for the MIN aggregation.

So so far what I’m doing, to compare this calculation to the view below, is it’s going to run this logic at the visualization level of detail, which is currently discrete, month, and year. So it’s going to look at each one of those, and that’s what creates this value.

It’s then going to look to see if that value per month matches the maximum SUM of Sales across the whole window. And by default, that window is going to be computed across the view for each row. So for each row, it’s going to look across and see if the sales for each month matches the sales for the highest value in the entire region. If it is, it will be labeled as MAX. Everything else will not be categorized.

I’m going to add a second line of logic that says if the SUM of Sales equals the WINDOW MIN this time, the aggregation is still SUM of Sales on the interior of that function there. Then this will be my minimum value. And then otherwise, I’ll just put a catchall here that says label it as “Other”. And this type of logic always concludes with the word END.

So that is the formula. Again, we’ll share that in the related content below the video. But now that I’ve got that, if I drag it to Color, we should see three different colors, one indicating it was the highest value in the window, one indicating it was the lowest value, and then everything else.

I’ll go ahead and remap these, maybe give them some colors that my end user might expect to see when something is either the highest or lowest or other. So maybe green for maximum value, red for minimum value. And then other, I usually like to gray this out or give it some kind of neutral color, which I think just, again, gives it a nice aesthetic.

The values are there. I can see them, but they’re not really the focus of the visualization. Then I’ve got nice bright colors to point out when it was the highest value and the lowest value.

The next thing I’ll show you is how to make the labels on this dynamic. You might be thinking you could just toggle on labels and show labels for the MIN and MAX values because that is an option. But let me show you what the problem is with that approach. If I click Label and turn those on, by default, we see all labels. But again, there is an option to only label the MIN and MAX values.

When I choose that, you can see that the MAX values are working properly. But then I see a whole bunch of zeros. And that is a side effect to this aesthetic. To get that rounded look, we have to use the MIN (0) measure. Well, that means there’s a 0 for every single month in a year in my data set. It’s trying to display every single 0 because that’s the lowest value on the view. Not quite what I intended.

So first I’m going to turn off these labels again. And I’m going to make a calculated field called Label. And the formula is IF my MIN / MAX Categories equals MIN or my MIN / MAX Categories– and I’m just going to remind myself of the logic by clicking on it, making sure I have the same casing, which I do.

So if it equals the MIN or the MAX, then show the SUM of Sales. And what this formula will do is it will run this logic at the viz level of detail, so again, each month of the year. If it equals MIN or MAX, it will show the Sales values. Otherwise, the outcome is NULL. So we should see two labels per row unless there’s a tie. If you have two maximum values or two minimum values, you could see more than one.

But that is the formula for the label. I’ll click OK. I will drag that to the Label property, and you, sure enough, can see that we’ve got– let me make sure these can show when they’re overlapping. There we go.

Now we’ve got everything showing– well, we’ve got labels for each of the MIN and MAX values. But we do still see some weirdness there with multiple labels. That’s because, by default, we’re using a mark type of Line. And Tableau is labeling both the start of the line and the end of the line.

Fortunately, within the Label property, there’s also an option to change that so that you’re only displaying the mark at the start or the end. And we only need one or the other. In fact, in this case, we would only want the end of the line. So the start of the line is MIN (0). It’s going up to the SUM of Sales for each month. And we only want to show that last value.

So that helped us there. This is very close to what I would want to see. However, there’s one label that’s not aligned like I would like, this 45,640. What’s happening is that’s the highest value in the entire view, and Tableau is trying to help us by delivering the best y-axis range. But when it did that for the East region on the second row, it didn’t also allow enough room to show the label.

So I’m going to show you a trick for getting around that. I’m going to make a calculated field, and we’ll call this Axis Adjustment. And what you do is you take the WINDOW_MAX of whatever metric you’re looking at– I left out my aggregation there. And then you multiply it by some kind of additional percentage to provide a little bit of padding. This is a little bit of a guess and check, but I’ll start with 1.3.

So what that formula is going to do eventually is look at the highest value per row, and it’s going to multiply it by 1.3, which will increase it by 30%. If the axis is 30% taller, that should unlock enough room to then move the label back into the proper position. So I’ll click OK.

To get this integrated with the view, it needs to be added to the Detail property. You’ll see when I place it there, nothing changes on the view. But now that I’ve added it to Detail, that newly created calculated field will be available to me to add as a reference line.

So to do that I will right click directly on the axis and choose Add Reference Line. In this dropdown for value, I should see my Axis Adjustment. You can already see in the background that it did draw a line at 30% higher than the maximum value. The label is where I would like it to be now. From here, it’s just a little bit of cleanup. I actually don’t want to display a line at all. So I’ll turn that off completely.

We also don’t need a label. I’m essentially just hiding that reference line altogether. It is technically there. You could run into it with your mouse, but it gives you that– it’s kind of a hack to get the labels to display exactly as you’d like.

So what we’re left with here are rounded spark bars in Tableau. We’re displaying both the labels for the MIN and MAX value. And we’ve colored the values based on whether they are the highest value, the lowest value, or other.

This has been Ryan with Playfair Plus – thanks for watching!