Exercise: Sales and Month over Month Sales

Make Tableau do your math using table calculations

In this self-guided exercise, you will improve a sales by month line graph by adding a second row with a table calculation to compute the month over month difference in sales.

Hi. This is Ryan with Playfair Data TV, and in this self-guided exercise you’re going to be building a trend line that looks at Sales by continuous month. But then we’re going to take it a step further using table calculations. On that second row that you can see on the screen there, we’re using a quick table calculation called Difference to compute the month over month sales.

And for extra points, see if you can color that month over month difference in Sales by that same table calculation. Positive differences should be colored blue. Negative differences should be colored orange. Take a shot at that. Pause the video. When you’re ready to see me explain how it’s done, go ahead and push play I’ll come back and help you out.

All right. Let’s take a look at how this is done. If table calculations are new to you or if you ever come across something that’s new to you in Tableau, I always encourage you to break it into smaller elements, because you know a lot of this stuff. So for me, if I was looking at that exercise, I would start with the first row, which is just a line graph.

It’s one of those fundamental chart types. You should know how to build this by now. If you don’t, there’s several videos on Playfair Data TV to explain it to you. But I’m just going to build a line graph that looks at Sales by continuous month of Order Date. I always start with my measure. So I’m just going to double click Sales to get that onto the view.

I’m then going to right-click on my Order Date dimension, and drag it to the Columns Shelf. That allows me to choose both the date part, as well as if it’s going to be used as discrete or continuous before I even add it to the view. I will choose Month with the green calendar icon next to it, which tells me it will be continuous.

And there is the first row. To start the second row, we need the same measure of Sales on the Rows Shelf. There’s two ways to get Sales onto the Rows Shelf. We could drag it from the Measures area, or we could use my favorite shortcut in Tableau, which is to hold down the Control key while you click on a pill that’s already on the view.

And when you do that while you hold the Control key and drag, it creates a duplicate of that pill. So we’ve now got Sales on the Rows Shelf twice. Table calculations are added to measures. We’ve got two measures on the view currently. Sales is on there twice on the Rows Shelf. Because it is on the Rows Shelf twice, each of those gets its own set of marks, and I can edit those marks independently of each other.

One of the applications of this is I can add a table calculation to the second row, but not the first row. To add a table calculation to the second row, I’m going to click into the second Sales pill, and hover over quick table calculation. And the one that I’m looking for is called Difference. I’m going to click Difference.

By default, table calculations are addressed from left to right. So the Compute Using by default is going left to right. That’s actually exactly what I want in this case, because I’m computing a month over month difference. Whenever I’m making this chart type though, I like to change the mark type of the second row from Line to Bar.

Again, because these are their own measures, they each get their own set of Marks Cards, and we can modify those independently. So for the first row, I can leave that as is. But on the second row, I can change the mark type to Bar. So we’re getting a little bit closer again.

The next step is to color those bars by the month over month difference. Again, there’s two ways to do this. I could drag Sales to color. That gives me this sequential color palette. I want to point this out. The color right now does not have a table calculation on it, so it’s just being colored by its raw sales value.

Although the marks themselves have this delta symbol on the pill, that’s how I know there’s a table calculation taking place. So the marks themselves are computing month over month difference, while the color is just computing the raw sales values.

So one way to fix that is I can re-add the table calculation again. But this is yet another application of my favorite shortcut. So I’m going to start that over and show you an alternative. I can just hold the Control key, and click on the Sales pill that already has the delta symbol on it, and drag that to color.

So I got to skip a step there. I didn’t have to reinvent the wheel and add the table calculation again. I had already had the table calculation, so I could just create a duplicate of that pill. Put that on color. This got me really close, but notice all the different shades of blue and orange in our now diverging color palette.

Typically the grays don’t provide much value to me. So if I have a diverging color palette, I will often change the step size to just two. You can modify that by double clicking on a color legend. This will allow you to pick colors for the different ends of the spectrum, as well as changing the steps.

So the first thing I’ll do is bump this down to two, and just to show you what that looks like, I’ll click Apply. Now there’s just one color for positive values, one color for negative values. But I can also modify the colors on the ends of the spectrum by double clicking each.

On the low end, I’ll use this orange color. And for the high end or positive values, I’ll use this blue color. And this has just been one of my favorite chart types. I love line graphs as is. It’s one of my favorite chart types. But what we’ve done just now is take that a step further using the power of table calculations to make Tableau do some of the math for us.

For example, one of the insights that’s standing out to me from here to here, so February to March, at the beginning of this Sample Superstore dataset, with just the line graph, we see that there is a big spike. But with the use of table calculations, we’re able to see that that spike represents exactly– I’m looking at the tooltip here and hovering over the blue bar–

That spike is exactly $51,171. So Tableau is doing some of the math for us because of that table calculation that we applied.

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