Sign in or start a free trial to avail of this feature.
5. Calculated Fields Part 1
Formulas that will be re-used regularly should be stored in calculated fields. In this lesson, we create a new field called Price Per Unit and explore some of the risks in using calculated fields.
To explore more Kubicle data literacy subjects, please refer to our full library.
- Used to store new fields that are not included in your current dataset
- Often stores more complex formulas that are difficult to write in a shelf
- Very convenient for holding re-usable formulas to avoid re-writing the same formulas
How to create calculated fields
- Go to the Analysis menu and hit Create Calculated Fields
- Enter a name for your calculated field
- Enter a formula for your calculated field
- Click OK if no errors occur
- Calculated field will appear in the data pane with an '=' sign next to the icon
If your underlying dataset does not include all of the fields that you need to answer your questions, you can create new fields in Tableau and then save them as part of the data source. For example, you could create a new calculated field called Profit that calculates the difference between Sales and Cost. You could also create a formula that calculates the difference between actual sales versus budgeted sales.
We create calculated fields in Tableau by defining a formula that is based on the existing dimensions or measures, and these values can be manipulated using standard functions and operators, such as divide, multiply, plus, or minus. To create a calculated field, we can simply go to Analysis and Create Calculated Field.
Let's create a calculated field called Price per unit, and this calculated field will simply take Sales and divide by Quantity to give us the price per unit for each row of our data. When we have a mistake in our formula, for example, when I remove a bracket, Tableau actually helps us identify where the error occurs. And this makes writing formulas quite easy, often easier than in Excel, because the description is actually very helpful. So, to fix this, I'll simply close the bracket. To create the calculated field, I'll simply press OK. And now, in my measures, I have price per unit and an equal sign next to the icon, indicating that this is a calculated field.
When we create a calculated field, like price per unit, Tableau also adds this column to our data source, as you can see on the right-hand side.
And this measure can now also be used in our visualizations. So, I can take the price per unit, and I can create a bar chart for different values.
In this example, I'll change the function from Sum to say Average, and this gives me the average price per unit based on each row in my dataset. Bestane is 3.729, and Medali is 0.550. However, with calculated fields, you need to be very careful in understanding what figures are actually being shown on screen. If we think about Bestane for one moment, we can see that the average price per unit is 3.729.
And to come up with this calculation, it took the price per unit, which we can see in our data source, and found the average of this column. Now, unfortunately, this isn't correct because we have different quantities for different months. And, as a result, to find the actual average, we need to take the sum of all Byresta sales divided by the sum of the quantity. And so, in actual fact, the overall average that we're going to be calculating in this sheet is different to the average of the price per unit. So, let's create a new pill.
And this is simply going to be the sum of all sales divided by the sum of quantity.
When I apply this, I can see that there are many differences between the two columns.
And, in fact, the second formula is correct when calculating the average price per unit across the full dataset.
So, while calculated fields can be of value, it's important to understand what's actually being calculated, potentially with some hand calculations to make sure that you understand what's going on. In the next lesson, I'll show you how to add some functions to your calculated fields and help you avoid some common pitfalls on this topic.