How to Make a Biggest Movers Chart in Tableau

See the Largest Period over Period Changes Impacting Your Business

A biggest movers chart displays the greatest changes in rank order – whether the changes are positive or negative. You will learn how to make this visualization plus a trick for displaying custom formatted triangles to indicate whether a change was positive or negative.

Calculated field to isolate current month’s sales based on a parameterized integer:
SUM(IF MONTH([Order Date]) = MONTH([Current Month Parameter] THEN [Sales] END)

Calculated field to isolate comparison month’s sales based on a parameterized integer:
SUM(IF MONTH([Order Date]) = MONTH([Comparison Month Parameter] THEN [Sales] END)

Calculated field to compute a month over month percent change:
([Current Month’s Sales] / [Comparison Month’s Sales]) – 1

Hi, this is Ryan with Playfair Data TV. In this video, I’m going to show you how to make, what I call, a biggest movers chart in Tableau. This chart type allows you to visualize period over period performances in a business on the same chart, whether they are positive or negative.

Here’s what it looks like on the screen. This is what we’ll be recreating over in Tableau Desktop. So as you can see, the biggest focus is on the largest changes. The changes can be positive or negative. But this chart is sorted in descending order with the largest changes first, all the way down to the lowest changes last.

The tricky part about that is getting them both sorted in descending order on the same chart, whether they’re positive or negative. But I think this has a lot of value to a business, particularly as a descriptive or explanatory starting point for a dashboard. So that’s what we’re going to go in to make in this video, over here in Tableau Desktop, using the Sample-Superstore dataset.

The first thing I’m going to do so that you can follow along and just to simplify the example, we’re going to create a parameter that kind of isolates our current month, what we’re calling our current month, and our comparison month. So what I’m going to do is create a parameter. And I’ll call this my Current Month. And we’ll make it a data type of Integer with a range of values from 1 to 12. Each of those integers represents one month in a calendar year.

By the way, I often do this, I often use month over month comparisons this way when I’m doing video tutorials here. But there is a much more dynamic way. If you want to check that out, that’s on another video. Just search for how to isolate comparison date ranges. And that’ll show you a more dynamic way to do this. Because parameters are static. I’m just doing this for ease of illustration.

So current month is done. Now that we’ve got current month, I can simply duplicate that. Edit the name. And I will call this Comparison Month. And the settings can be the same. Integer data type, minimum 1, which is January, maximum 12, which is December.

Now we need to make the key calculated field that computes the month over month difference. I often like to use 100-point index scores. But most companies are used to looking at this as a percentage change. So that’s how we will write this formula today. And I’ll call this Month Over Month Change. And the formula will be SUM IF the Month of Order Date, which is the name of the date field in the sample dataset equals the Current Month parameter, then Sales END.

And I’m going to copy that formula because that will be the foundation of the denominator with one change here. We’re going to compare the Current Month to the Comparison Month. And I’ll share this in the related content below the video in case you need it.

And then if we want to do a percent change, I’m going to add minus one at the end. And just to be safe on the order of operations, I’m going to wrap the month over month comparison. So that is the formula. Again, that’s a shortcut. There’s a more dynamic way to do this on a different video. But if you like that percent change calculated field, I will also share it in the related content below the video.

Going to go ahead and click OK. And because this is a calculated field, it comes with what’s called Automatic number formatting. So if I were to add this to the view, actually add that to the Columns Shelf, well, first of all, we see a zero because we’re comparing January to January. Both the current value of the Current Month parameter and the current value of the Comparison Month parameter started out as one. So we’re just comparing January to January.

To make a more realistic scenario, I’ll bump up the Current Month to 2, which is February. And actually, let’s change these to 12 and 11 because I’m just thinking February has fewer days in the month. We might get some weird results. So let’s compare December versus November, thinking maybe we’re evaluating some type of Cyber Monday promotion. So December for my Current Month. I’ll go into my Comparison Month and change that to 11.

OK. Now back to what I was mentioning with the automatic number formatting. It looks like we’re coming in between minus 7.5% and minus 8% overall. But because this is a calculated field, we’re just starting out with these decimals. Because this is meant to be a percent change, what I’m going to do is apply some default formatting to my Month over Month Change calculated field.

This should probably be a percentage. And I’ll just go with no decimals for now. Click OK. And now, as you can see, that’s a lot more easy to read. We’re between minus 7% and minus 8%.

The next thing I’m going to do is drag the Sub-Category field onto the Rows Shelf so that we can get a feel for the month over month change by sub-category. And just to make this easy to quality check and kind of follow along, I’m going to click Show Me and convert this into a table. OK. So we’re getting closer here. We’ve got our positive changes and negative changes represented on a single view. But watch what happens when I sort these in descending order.

Of course, we see our positive changes first, all the way down to our largest negative changes at the very bottom. But here’s the point of this chart. What’s interesting to me are the categories, such as Supplies, which had a very large positive month over month increase. And Machines is actually the second largest change, although that change was negative. It’s negative 55%. So because it’s the largest negative change, it was put all the way and kind of hidden at the bottom.

I’d rather move that all the way up to the second slot, but my sort order is not allowing me to do that because it’s going to take all the positive changes first, followed by all of the negative changes second. So here’s the trick to it. We’re going to duplicate our Month over Month Change calculated field. But I’m going to make this second version absolute. And that’s very easy to do.

I’m going to just change the name of this to Absolute. And that actually is the function. And all you’d have to do is wrap this entire formula in that Absolute function. And that will take all of your negative numbers and convert them to positives.

I left the formula in there so you could see it. But in the real world because we’ve already created this Month over Month Change element, we don’t technically need to leave this entire formula in here. We could just say take the absolute of my Month over Month Change. That would be a lot more elegant way to write that formula. It’d be a lot more easy to manage this and know how to troubleshoot if something goes wrong.

But we’re just looking at the original formula currently on the table. Converting the negatives to their absolute counterparts. Going to click OK. I’ll double-click on this to add it to the view.

And now, we’re going to sort by the first column instead of the second column. So in the first column, we’ve got our absolute versions. That allowed Machines to move up to the second slot. On the right, I left in the original values just so you can see whether they are positive or negative.

Now, we’re ready to move on to creating the triangles that appear that precede the values. For the triangles, we want to start out with our original Month over Month Change. Because what I’m going to do is generate an up triangle if the change was positive and a down triangle if the change was negative.

Notice, obviously, when we converted those values to absolute, we lost the ability to see whether they are positive or negative. But that is very important context. So instead of representing it with a negative sign, I’m going to get triangles in there. It’s a little more engaging.

And we’re also going to make these calculated fields separately, which will allow us to color them and format them independently of each other. We can make one triangle red. Can make another triangle green or blue.

Because we’re starting with the foundational Month over Month Change calculated field, I’m going to right-click directly on that calculated field. Hover over Create. And click Calculated Field. And I will call this my Positive Triangle.

And this is going to have just two outcomes. If the month over month change is positive, I want to see an up triangle. If it’s negative, I don’t want to see anything. Anytime I’m making a calculated field that has just two outcomes, I like to use this immediate IF statement. It’s spelled I- I- F. Then I will open the parentheses.

And we’ll say if that Month over Month Change was greater than or equal to zero, then I want to generate this up triangle. The up triangle is what’s called an alt code character. And you can create it by holding the Alt key while you click 3, 0 and let go.

The reason I put that alt code character within those tick marks is because it’s technically a string. Tableau is looking at that as a character in the data. So it needs to be a string, comma, and then we’ll just say NULL.

So how this immediate IF statement works is it looks at the criterion before the first comma. And if that’s true, the outcome will be what’s in between the first and second comma. If it is false, it is what is after the second comma. All right? So that’s so far so good on our positive triangle. I’ll Click OK.

I will now duplicate that positive triangle and turn it into a negative triangle. So just a couple of changes here. Obviously, I need a new name. So this will be my negative version. I also want to change the direction of these greater than or equal to signs.

For this one, I’ll just say if it’s less than zero. And instead of an up triangle, I’m going to use a another alt code character, which you can create by holding the Alt key while you click 3, 1, and let go. That creates the down triangle.

So again, positive triangle is Alt 30. Down triangle is Alt 31. So this one looks at this criterion. If it’s less than zero, we see a down triangle. Otherwise, we see a null. Going to click OK.

We’ve now got everything we need to create this view. So the next thing I’m going to do is drag those newly created triangles onto the Text Marks Card. That’s what will allow them to show up and allow me to format them independently of each other. So I’m going to drag Positive Triangle to Text. And I’m going to drag Negative Triangle to Text.

And you can do some quick spot checking here. Because I left the original values on the right, I can look down that second column to determine if it’s ever a positive value, I should see an up triangle. If it’s ever a negative value, I should see a down triangle. And just glancing through this, on the rows, it looks pretty good.

Now that I’ve kind of QAed that a little bit, I’m going to go ahead and get rid of the second column because that’s going to distract from the view. My user won’t know exactly what they’re looking for or which column they should focus on. So we’ve cleaned this up to one column. But we have the confidence to know, we’re seeing an up triangle anytime the change was positive, a down triangle anytime the change was negative.

I’m going to click the Text Marks Card. And because we have something on that card, we have the ability to click this ellipsis, which will open a little word processor where we can format some of these things. The number one thing I want to do is put these triangles in better relation to the value. And what I mean by that is I’m going to put them in front of the values. So I’m just cutting and pasting so that these line up on a single line preceding the value.

Anytime you see the Apply button in Tableau, you can click it to preview the change. That looks decent, but maybe I’ll add at least one space between the triangles and the value. And maybe I’ll also make these triangles just a little bit smaller. So I’ll make those eight point font. And I’ll make the values 10 point font. Let’s apply that again.

And that’s looking a little bit better. We see one thing that kind of got thrown off there just because the supplies change was so large that it didn’t quite have room to fit that on there. But we’ll call that good for now.

And, again, just to point out what those immediate IF statements were doing. The reason, even though you see both triangles represented on the Text Marks Card, you’ll only ever see one per row. If it’s a positive change, you’ll see an up triangle. And the down triangle is essentially null. And if it’s a negative change, you see the opposite of that. You see a down triangle, but the up triangle is null. So that’s what’s getting this to work.

But the reason I did those independently is I love this ability to edit them independently. If it’s positive, I don’t love the colors green and red, but just for illustration because so many people think about it that way, I can make my positive triangle green and my negative triangle red. Those are completely independent.

If I click Apply, you’ll see those changes represented on the view. Let me just close this up a little bit. We’re looking pretty good here. I see some extra spacing. I’ll just go delete some of that. Click Apply again. Here we go.

Make these rows a little bit taller. And this is a pretty good biggest movers chart already. This represents all 17 sub-categories in the Sample Superstore dataset.

Just to show you one more thing that’s a nice addition to this chart type, I do explain this on another video, but an easy way to do row filtering is with what I call index filters. So I’ll call this my Top 10 Changes. And the entire formula, if I wanted to look at just the top 10, is INDEX open parentheses close parentheses is less than or equal to 10.

INDEX() is a table calculation that’s synonymous with row number. So this is saying– and how I wrote this is a Boolean formula. So you’re either in the first 10 rows, or you’re not. I’ll click OK. And once I add that to the Filters Shelf, and click True, we should just see 10 rows represented. And those are my 10 largest changes in the business between December and November in the Sample Superstore dataset by sub-category, whether those changes were positive or negative.

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