Online tableau training, tableau tips, & video tutorials
An Introduction to Tableau Calculated FieldsPreview
Learn to compute new fields from existing fields in Tableau
This video walks you through how to combine two measures to create a new measure in Tableau. You’ll also learn some common pitfalls you may come across when creating calculations in Tableau.
Hi, this is Ryan with Playfair Data TV. In this video, we’re going to talk about one of the most important technical features of Tableau, which is calculated fields. I always say that if you can master the calculated fields parameters and dual axis combination charts, you can unlock almost all the flexibility that Tableau provides you. It allows you to do most of the cool tips and tricks and tutorials that we’re going to be covering in other areas of Playfair Data TV.
To get started with this, I’m going to show you a typical chart you might come across in a real life scenario. You might be looking at a trend for something like sales and profit, like you’re seeing here in Tableau Desktop. As you can see on the chart, these measures, as you would expect, tend to follow a similar pattern. If we have a spike in sales, we most likely have a spike in profit. If we have a dip in sales, we probably have a dip in profit.
What I would really like to know instead is, of my sales amount, how much of that is profit? In other words, what is the profit ratio? And the formula for that is the sum of the profit divided by the sum of the sales.
But if I look over here in the rawest form of the sample Superstore data set which we’ve been working with so far, I don’t see any measures for profit ratio. I see profit and sales individually, but I don’t see those together. I don’t see profit ratio. That’s what we’re going to do in this video. We’re going to combine those to create something new out of these existing measures.
There are several ways to start a calculated field in Tableau. My preferred way is to right click in any blank space on the Data pane. If you don’t see much blank space, you can resize the Dimensions area of the Data pane and the Measures area of the Data pane by left clicking between them and dragging this up a little bit.
I then just right click in any blank space and click Create Calculated Field. However, there’s always more than one way to do the same thing in Tableau. We could also click this down arrow in the top right corner of the Dimensions area of the Data pane, and the first option is Create Calculated Field. You could have also clicked Analysis, and the fourth option from the bottom is Create Calculated Field. I’ll show you a fourth way in a future video, but for now, let’s go with my preferred way, which is to right click in any blank space, and click Create Calculated Field.
First thing you do is give this a name. This is going to be called “Profit Ratio.” And I’m first going to show you the correct way to do this, and then I’m going to show you one of the most common pitfalls of calculated fields second. But I want to show you this the correct way first. Hopefully this is the one that you commit to muscle memory.
But the full formula for profit ratio is sum of profit divided by sum of sales. So I’m first going to type the aggregation for the numerator, S-U-M for sum, open parenthesis. There are even several ways just to get the measure of profit into this calculated field dialog box. I’ll show you my preferred way first, and then I’ll show you a couple of other methods.
I prefer to start to type the name of the measure, so P-R-O. You can see that Tableau is autofilling to show me which fields start with those characters. I then click on the one that I want to add to this calculated field.
When it turns orange, that means that Tableau has recognized it as a field in your underlying data. That’s my preferred way. That’s the way I find the most foolproof method of getting what I want into the Calculated Field dialog box.
And I’ll show you a couple of other ways. If you know exactly how the measure is spelled, you could also just type out the entire name of the measure– P-R-O-F-I-T. If it turns orange, that means Tableau recognized that as a measure in your underlying data.
If you don’t want to type it out, you can also just drag the profit measure from the Measures area of the Data pane into that Calculated Field dialog box. It’s in there. Whichever method you choose, we’re going to close this with a closed parenthesis.
We’re then going to add our operator, which in this case is a division symbol. So I add a slash, and now we’re going to do the same thing for the denominator. First, the aggregation, S-U-M, open parenthesis, and then my preferred method. I start to type the name of the measure, and then I click on it. Close parenthesis.
Spaces and hard breaks do not matter between operators. So this that you see there is the same thing as this, which is the same thing as this. You’ll get your own habits. The choice on how you decide to space and break this up might come down to how complex the formula is.
With this being such a short formula, I would most likely end up with something like this– sum of profit divided by sum of sales. Now that we’ve got our calculated field, I’m going to click OK, and we see something new show up here on the Measures area of the Data pane.
We just made this, and it has an equal sign before it. That’s what tells us it’s a calculated field. If you ever see that equal sign on either a measure or a dimension, you can right click on it and click Edit. That’ll open up that same dialog box so you can make some modifications to it.
Now that we’ve got that, the first thing that I do– this is a very critical step with a calculated field– is I quality check that calculated field. My method for doing this with measures is to simply put it onto the view and do the math. And because profit ratios should probably be in terms of percentage, I’ll also go ahead and set the default formatting.
Note by default on this y-axis you can see there, the number, by default, will have automatic formatting. So if you want to do something like add a currency symbol or a percent sign, you have to do one extra step to it, which is to right click on the calculated field, hover over Default Properties, click Number Format, and choose Percentage, in this case. I’ll do Percentage. Two decimals is fine, and I’ll click OK.
And now, like I said, I’ll just do the math to make sure that this is calculating properly. I’ll open up a calculator and I’ll look at one of the months. So in January 2015, profit was 2,450 divided by sales of 14,237, and then the answer is 0.1721. I’m going to multiply that by 100, which would be the equivalent of putting this into percentage terms.
And the answer we’re looking for is 17.21%. Sure enough, we can see there in the Tooltip that the answer is 17.21%. So excellent– now that I know that’s working, I can use this profit ratio calculated field anywhere I would like, just like all the other measures. I could start a new worksheet, I could use profit ratio by itself, and slice and dice it by segment.
So here’s an insight that we didn’t have visibility into before. Home Office is leading the way– I’ll go ahead and show the label so we can see this– Corporate is second and Consumer is third in profit ratio. That was not visible to us before we made this brand new calculated field. If I’m the manager of the Home Office segment, I’d very much want to know that we’re leading the way. A new insight that has emerged because of our use in this– benefit of using calculated fields in Tableau.
I’m going to take a step back and show you what many beginners do when they’re making a calculated field for the first time. It’s a very common pitfall, so I’m going to make a new calculated field and I’ll call this one “Profit Ratio Wrong Way.” And the formula this time– if you were to just try to talk this out loud and create this, you might say, well, the formula is profit divided by sales. So maybe you would just type that. Profit divided by sales.
And Tableau even says there that the calculation is valid. I want to point out that when it says it’s valid, that does not mean it’s going to give you the correct answer. It means that you’ve met the technical criteria in order for Tableau to compute and do the math. It doesn’t mean it’s the right answer. That’s why quality checking your calculated measures is so important.
I’ll click OK, and I’ll add this as a fourth row, and just to have an apples to apples comparison, I’ll also put this in terms of percentages. And we already QA’d it. We know that the answer is supposed to be 17.21%. If I hover over the Tooltip again and look at the wrong way, the answer for January 2015 is 1,015.8%. That’s very inflated, very incorrect.
So I want to point this out so you don’t get in trouble if you try to make a calculated field. I don’t want you to walk into a meeting and say, hey everybody, we made 1,015% profit ratio last month. Can I get a bonus? You don’t want to go there. Just make sure you quality check your calculated fields before you implement them into your dashboards.
And by the way, the reason that that happens has to do with the aggregation of the formula. So this is what it was doing when it was computing the profit ratio the correct way. This is just a sample of the first five rows of data. It doesn’t matter which data points we pulled from.
But just to illustrate what this is doing, when it does a calculated field the correct way, it’s going to look at– it’s going to sum or do whatever aggregation you put in there at the viz level of detail. The viz level of detail for that chart that we created was month. So for each month, it’s first going to do that aggregation for profit to create our numerator for each month.
It’s going to sum up every single profit value for the month of January. It’s then going to do the same thing for sales to create our denominator. Then it’s going to divide those by each other last to get us the correct answer, which in this case would be minus 5.64% for just these five rows.
When it was doing it the wrong way, it was doing the formula correctly, but it was doing it for each row individually. If you were to look at each one of these rows, it would be fine, but what it was doing at the end, then, is adding all of those up together. That’s why it was so highly inflated. It would do profit divided by sales for 9,994 records, then add all those together. Not quite what we wanted.
There was one more clue that it was aggregating these differently back here on the visual. Note when we did it the correct way, we see a new aggregation called AGG. That’s Tableau telling us that that’s an aggregate. It’s already being aggregated in the underlying formula.
When we did it the wrong way, we see SUM again. It’s summing up all those profit ratios. So that would have been another hint that we had not quite done this the right way. So I’m going to remove that, and I’m going to show you just a couple more things about calculated fields that you might run into.
I’m going to modify the calculated field for the correct version, just to show you a couple of legitimate error messages that you might run into. This is going to help you write these calculated fields. So, for example, if I left off the parenthesis at the end, instead of saying the calculation is valid, Tableau tells me this calculation contains errors in this red error message here.
These error messages are usually pretty good. If you click on this, you can usually get a pretty good hint about what’s going on. It says, “Expected closing parenthesis or comma while parsing argument list for sum.” It also puts a red squiggly line near the error. So I see that red line. I also see it says, “Expected closing parenthesis.” Those are pretty good clues to remind me, oh, I forgot to close this out with a closed parenthesis.
Another one that you might see– this one seems obvious, but if you don’t have an operator– so if we didn’t divide these by each other, Tableau will yell at us here. Click on it and it says, “Syntax error.” Maybe you’re missing an operator. Exactly, there’s a red squiggly line near where we’re missing the operator. You can go correct that to get back to a valid formula.
The most confusing error message that you might run into is that you cannot use a mix of aggregates and non-aggregates. What I mean by that is you couldn’t, for example, aggregate the numerator but not aggregate the denominator. So, for example, if I were to remove sum from the denominator, we’re now mixing aggregates, and if I click on the error message, it says, “Cannot mix aggregate and non-aggregate arguments with this function.”
So we’ve got a mix. So we would need to go correct that and make them consistent. They can either all not be aggregated, or they can either all be aggregated. This will come up a lot as our formulas become more complex.
But for this one, as I just showed you the wrong way to do the aggregation versus this correct way, I’m going to go ahead and aggregate both the numerator and the denominator. This, by the way, would be very similar, since so many of you are familiar with using Excel to do something similar. This would be just like if I were in Excel, and in cell A1 I had profit, in cell B1 I had sales, and then in cell C1 I typed out equals A1 divided by B1.
It would be very similar, but why Tableau is so much more powerful is in Excel, I would have to go drag that every single place that I wanted that formula to be applied to. And it would be very error prone. I’d have to make sure that that formula was being applied to the correct rows.
In Tableau, you make the calculated field one time, make sure you quality check it so you trust that calculated field, but then you can use it over and over again just like you would use any other measure– an extremely powerful technical feature of Tableau.
This has been Ryan with Playfair Data TV – thanks for watching!
Related video: 5 Ways to Make a Bar Chart in Tableau and An Introduction to Aggregation
Related video: An Introduction to Tableau Parameters
Related blog post: An Introduction to Calculated Fields in Tableau
Related chapter: Practical Tableau – Chapter 12 – An Introduction to Calculated Fields