An Introduction to Tableau Table Calculations

Control your data tables by understanding addressing and partitioning

Learn to use table calculations to benefit from: (1) using advanced calculations without knowing all the syntax, (2) learning code by reverse-engineering a calculated field, and (3) speeding up processing time by reducing the number of rows in a computation.

Hi. This is Ryan with Playfair Data TV. And in this video, I’m going to be providing an introduction to table calculations in Tableau. Table calculations are a special type of calculation that provide a lot of benefits. One is it allows you to do some fairly advanced calculations without having to know necessarily how to write the syntax to create those calculations.

Another advantage to table calculations is they are very efficient. They’re processed on a subset of data. And when you condense the number of rows in your data set for Tableau to have to do the math on it, the processing will be a lot faster. And the third benefit I’m going to show you is a way to reverse engineer table calculations so that you can learn the syntax.

To help illustrate what table calculations are, how to add a couple of these, and to learn the syntax, we’re going to start with this table over here in Tableau Desktop. I do want to point out before I start that, even though they’re called table calculations, they don’t have to literally take place on a cross-tab view in the data. I’m going to show you this later in a different video, but you can use table calculations on any chart type.

The first thing to know about table calculations is they are defined by how they are partitioned and how they are addressed. And those two classifications happen on dimensions. To show you the first one, I’m going to add a table calculation to the Sales measure on this view. And in fact, table calculations are always added to measures. And right now, I only have one measure on the view. So that’s where we’re going to start.

I’m going to right-click on that Sales pill. And there are two things here at the bottom related to table calculations. The first one says Add Table Calculation. This would allow you to add a table calculation manually. The second one says Quick Table Calculation. These are often-used table calculations. These are kind of preset table calculations that are much easier to add. You don’t even need to really know what you’re thinking about in order to add it. You just need to know what the name of that table calculation is. And you can see, if I hover over it, I’ve got several options.

Just to give you a couple of examples of these, I’m going to start by choosing Running Total. And when I click that, notice the numbers in the background are going to update a little bit. Click Running Total. You saw some numbers change. Specifically, you saw the numbers change in the second and third column.

Right now, what this table calculation is doing is moving left to right across the table. And since we chose Running Total, it’s adding the running total of the numbers before it from left to right. So the reason the first column didn’t change is there was nothing before it to add to that running total. By the time it got to the second column, it was adding the number for Office Supplies to the number for Furniture. And by the time it got to the third column, it was adding the value for Technology to the running total that had already been established, Furniture plus Office Supplies.

Remember, there’s two ways that this is classified. One is the partitioning and one is the addressing. The direction in which this table calculation is moving, so left to right, is called the addressing in Tableau. Tableau’s word for that is Compute Using. That’s how often the table calculation starts over. Partitioning, you could think of as a “group by.”

Both of these classifications are going to be based on a dimension. We’ve only got two dimensions on the view at the moment. And you can also only be one or the other. So if my addressing is going left to right across the Category dimension, that leaves the Month of Order Date dimension as my partitioning field.

The specific table calculation that I added for this example doesn’t make a whole lot of sense. The addressing is moving from left to right. So by the time I get to that third column, that is the running total across each of my three categories per month. I would much rather know, what is my running total for each category by the time I get to December?

So I’d rather change how this is being addressed. Instead of going left to right, I’d rather it go top to bottom. Well, you can change the addressing of a table calculation by clicking on it and making an edit.

Now that we’ve got a table calculation in place, there is a delta symbol on our Sales pill. That’s what tells us there’s a table calculation taking place. And if I click into this pill again and hover over Compute Using– remember, that’s Tableau’s word for addressing– I can change the direction of this table calculation. By default, it’s moving from left to right. Remember, that was the example that I said didn’t make a whole lot of sense.

Instead, I will choose Table Down. And you see the numbers shift again. And now this makes a lot more sense. It’s now computing from January Furniture, and then down in each column so that by the time I get to each column in each respective category, I’ve got the running total for the entire year. This makes a lot more sense.

You can also add these table calculations manually. I’m going to clear this one out to start over. And I’m going to click into the pill. And this time, instead of using one of those preset options, I’m going to choose Add Table Calculation.

This will bring up a dialog box. And you can see the highlight in the background is kind of trying to help show me how this is being addressed and partitioned. But if I chose– I’ll leave it at Difference From for now. And again, it doesn’t make a lot of sense for this to be computing left to right. I’m going to change the addressing to go Table Down.

So this is how you can add a table calculation manually. But this is also available as a quick table calculation. If you don’t want to go through those steps, you can just click into the pill, hover over Quick Table Calculation, and the one we’re using in this second example is called Difference.

I think that these quick table calculations are perfectly fine for a beginner, as you’re getting started. Again, this will unlock a lot of fairly advanced calculations that you can use without having to need to know how to write the syntax. Once you need to get really specific about how you’re partitioning fields and having the calculations start over again, you might need to advance up to the add table calculation manually instead.

Let’s take a closer look at this Difference calculation, though, and what’s happening. Notice that this time my first row is null. We chose Difference. So what it’s doing is it’s taking the number in each cell minus the number in the cell above it.

And that’s why this addressing concept is so important. If you’re moving left to right, the value in the cell before the current cell is in terms of columns. If the addressing is going top to bottom, that difference in cells is in terms of rows.

So in this example, we were moving from top to bottom. It would take the value in February’s cell minus the value in January’s cell. And the reason January is null is because it has nothing in front of it to subtract it from. The addressing is going down, and there’s nothing in front of January to subtract. Essentially, what we’ve just computed is a month-over-month difference.

One more thing I want to show you about table calculations– notice, again, a table calculation is taking place if there is a delta symbol on the pill. And if you double-click on that pill that has that delta symbol, Tableau gives you this kind of X-ray vision to look into that pill and see how the formula is being computed.

Well, what’s really nice about this is I can just copy out that formula, start a calculated field, and paste that formula into this box. And there is the calculation. Like I mentioned, it’s doing some fairly advanced computation on this without having to need to know some of these functions and different types of calculations that it’s doing.

Any time you’re in a calculated field dialog box, and you’ve got this little data dictionary on the right-hand side expanded, which you can do by just clicking this right arrow– if you click on anything that’s blue, that function will be looked up. You’ll get a little definition for what that function does as well as a way to use it in the syntax.

So we see a couple of examples here that aren’t common in a lot of calculated fields. So I’ll just point out how you might learn from this by adding a table calculation. We added this table calculation of Difference. We double-clicked on the pill to copy the formula out. I’ve just pasted it into this calculated field dialog box.

And now, if I click on something that’s blue, such as ZN, it looks up that function. And it tells me some information about it. It says returns the expression if it is not null. Otherwise, it returns a zero. And then there’s the syntax for how to use the ZN function. That’s just like we’re using it here with Sales, but we’ve got Profit in the data dictionary as the example.

Essentially, what this is doing is it’s looking to see if we’ve either got a number in the cell or a null. If there is a null, it plugs in a zero. ZN actually stands for Zero Nulls. It’s a way just to guarantee we can do the math.

Another one we see that’s a little bit different is LOOKUP. If we click on that, you’ll get a long definition. I’m not going to read you this one word for word. But essentially, what this does is it looks up the value based on an offset. So it’s looking up the Sales value, in this case– minus 1 is the offset.

And again, this comes back to the addressing. That’s why it’s so important to tell Tableau which direction you want this computation to take place. If we’re going left to right this minus 1 is in terms of columns. And if we’re going top to bottom, this minus 1 is in terms of rows.

And one last thing on table calculations– we saw how fast this process, when I added those– and yes, this is the Sample Superstore data that only comes with 9,994 rows. But these are also very efficient even if you’re dealing with larger data sets. Because that difference– in this case, that month-over-month difference– would just be happening on these 36– I’m looking in the bottom left corner of the authoring interface to see I’ve got 36 values on the view. It’s only having to do the math on that very small subset of data to generate this view.

So that was just an introduction to table calculations and just describing some of the benefits. One, it allows you to do some fairly advanced calculations without needing to know how to write the syntax. Two, you can learn that syntax, though, if you copy and paste the formula into a calculated field dialog box. And three, these calculations are among the most efficient in terms of how fast Tableau can process and give you a result.

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