An Introduction to Calculated Fields in Tableau

An introduction to calculated fields in Tableau

To bring the concept of calculated fields to life, let’s pretend we are working with the Sample – Superstore dataset, and want to evaluate the average order value (AOV) for the product sub-categories we manage compared to the rest of the company. AOV is defined as total sales revenue divided by the number of orders. While this dataset has Order ID as a dimension, it does not have the number of orders as a measure, which is the denominator for the AOV calculation.

To isolate the number of orders, I will first create a calculated field to count the number of orders. The formula to count the distinct number of orders will be COUNTD of the Order ID dimension. To start a calculated field, you can either:

– Click the down arrow in the top-right corner of the Dimensions Shelf and pick “Create calculated field…”
– Right-click a blank space on the left sidebar and choose “Create calculated field…”
– Click Analysis in the top navigation and choose “Create Calculated Field…”
– Right-click on one of the fields you want to use as part of your calculated field, hover over “Create”, and choose “Calculated field…”

For this first calculated field, because I know that I need the Order ID as part of my calculated field, I will start the calculated field by right-clicking on the Order ID dimension on the Dimensions Shelf, hovering over “Create”, and clicking “Calculated field…”. This opens a new dialog box where I can enter the formula for my calculated field:

On the right-hand side of the calculated field dialog box there is a definition of the COUNTD expression. Any time you see a blue color-coding when creating a calculated field, you can click on the blue-colored word to get a definition and example of that particular function; this is a great way to learn the syntax. After clicking the OK button, you will see a new measure for Orders appear on the Measures Shelf – a brand new measure that we can now use throughout the workbook!

Now that I have my Orders measure, which is the denominator of the AOV calculation I’m after, I can create another calculated field to calculate the total sales revenue divided by the number of orders. To start this calculated field, I will right-click on a blank space in the left sidebar and choose “Create calculated field…”. I’m presented with a blank calculated field dialog box where I can enter my formula for AOV:

Notice this time that the measure of Sales is aggregated as SUM, but Orders appears to have no aggregation. That’s because Orders already has an aggregation of COUNTD in the underlying calculated field that we created in the previous step. Tableau does not allow you to mix aggregated and non-aggregated fields within a calculated field. If you create an invalid calculated field, Tableau will display a red indicator at the bottom of the dialog box that reads “The calculation contains errors”. You can click on the error message to be provided with a clue for what may be wrong with your calculation.

Also remember that even when Tableau says that a calculation is valid, that’s in terms of the syntax, and not in terms of the result you are looking for. To quality check a calculated field and ensure it’s properly calculating the result, I like to put the raw ‘ingredients’ on the view and calculate the answer manually. In the case of this tutorial, I would create a quick table showing orders, sales, and the newly created AOV measure. I would then divide the sales amount by the number of orders to make sure the correct answer is reflected as AOV.

Based on the image above, it looks like Tableau has calculated the correct answer: $2,297,201 in total sales divided by 5,009 total orders equals an average order value of $459. By default, calculated fields do not receive number formatting. In the case of AOV, which should be displayed as a currency, this can be changed by right-clicking on the calculated field on the Measures Shelf, hovering over “Default properties”, and clicking “Number format…”.

More on aggregating calculations

Aggregation is an important concept to consider when creating calculated fields. A calculated field for SUM([Profit]) / SUM([Sales]) will give you a very different answer than [Profit] / [Sales], even though both formulas are valid. If you do not provide the aggregation within the calculated field, Tableau will calculate the equation for every record in your analysis, then aggregate the answers for all of the rows together when the calculated field is added to the view. It is critical to quality-check calculated fields and ensure you are seeing expected results before integrating the new measures in your work.

To this point, we’ve created a new measure for Orders and used the Orders measure within a second new calculated field for AOV. Now let’s put the new fields to work by answering our business question:

What is the AOV of the product sub-categories I manage compared to everything else?

For the purposes of this illustration, I’ll pretend that I manage the Copiers, Machines, and Supplies sub-categories. To answer this question, we will create a third calculated field, this time creating a new Sub-Category Segmentation dimension.

This calculation is slightly different in that we will be incorporating IF / THEN logic to create a segmentation: one segment for the sub-categories I manage (Copiers, Machines, and Supplies) and the other segment for every other sub-category. The formula looks like this:

All this calculation is saying is that if the dimension member of Sub-Category matches copiers, machines, or supplies, I want the data to be classified as “My Sub-Categories”. If the dimension member is anything other than those three, I want the data to be classified as “Other”. To quality check a dimension calculation such as this one, I like to first place the original dimension on the rows shelf, followed by the newly created calculated field to make sure the dimension members are being properly calculated:

As you can see, my three sub-categories are classified as “My Sub-Categories” in the second column, and everything else is classified as “Other”.

I’m now ready to answer my business question by putting the AOV calculated field on the view, then slice and dice the measure by the Sub-Category Segment dimension:

The answer clearly shows the AOV for the product sub-categories that I manage is much higher than the AOV for all of the other sub-categories – maybe it’s time I ask for a bonus!

Thanks for reading,
– Ryan

This content is excerpted from my book Practical Tableau: 100 Tips, Tutorials, and Strategies from a Tableau Zen Master published by O’Reilly Media Inc., 2018, ISBN: 978-1491977316. Get the book at Amazon.