Tableau Calculated Fields: Creating and Aggregating Them

Wednesday 11, August 2021
Tableau Calculated Fields: Creating and Aggregating Them Blog Image
Tableau allows authors to create new data out of existing data using a calculated field. This method is one of the most powerful features of Tableau. Tableau calculated fields help create new dimensions. For example, segments, new measures(like ratios).

Calculated fields in Tableau can also be used with any sort of data type; various functions, aggregations, and logical operators, making them versatile.

This article will let you know everything you need to know about the calculated field to get yourself started. You'll also learn to create two new measures and one new dimension in a Sample - Superstore dataset.

Why is there a need for a calculated field?

  • Calculated fields in Tableau are a great way to segment your data quickly 
  • To get rid of unwanted results for better understanding and analysis of data.
  • To prove a new measure or dimension before declaring it a permanent field in the underlying data.
  • To leverage the power of Tableau parameters. It gives some choices to the end-user.
  • It saves unwanted database processing and storage resources by calculating ratios across many different variables.  

How to create a calculated field in Tableau


To understand things better, let's say we are working on a sample - a Superstore dataset.

This is a great Tableau calculated field example to wrap your head around the concept. We want to evaluate the average order value (AOV) for the product sub-categories we wanted to be compared to the rest of the company. AOV is the total sales revenue upon the number of orders. 

AOV = Total sales revenue ÷ Number of orders.

The Superstore dataset we are handling has an Order ID as a dimension but doesn't have orders as a measure. And we need it to calculate AOV.

To count the number of orders we have to create a calculated field Tableau. The formula we'll use to count the distinct number of orders is COUNTD of the Order ID dimension. 

To start a Tableau calculated field, do any one of these methods: 
  • On the top right corner of the Dimensions Shelf, click on the down arrow and select "Create calculated field." 
  • On the top navigation, click on analysis and select "Create Calculated Field." 
  • Hover mouse over a blank space on the left sidebar, right-click and choose "Create Calculated Field." 
  • Right-click a field you want as a part of the calculated field, hover the mouse over "Create" and click on "Calculated Field."  
As the Order ID is needed as part of the calculated field, right-click on the Order ID dimension in the Dimensions Self, hover over "Create," and click on "Calculated Field." This will open a new dialog box wherein you can enter the formula for the calculated field. 



On the right portion of the calculated field dialog box, you'll see columns containing several expressions; select COUNTD.

TIP: When creating a calculated field, if you see a blue color-coding, click on it. This helps you see definitions and examples to use that particular function. A great way to learn syntax in Tableau while creating a calculated field.  

Click on the OK button to see a new measure for Orders visible on the Measure Shelf - the new measure that can be used through the workbook now!

Now that we have what we need to calculate the AOV, we can create a new calculated field to calculate the total sales revenue divided by the number of orders. 

Right-click on the blank space in the left sidebar and select "Create calculated field..." Now enter the formula for AOV in the blank calculated field dialog box:  



Pay attention to how Sales are aggregated using the SUM expression, but Orders have no expression for aggregation. That's because Order intrinsically has aggregation of COUNTD in the previous step.

Within a calculated field, Tableau prohibits mixing aggregated and non-aggregated fields. If you do so, it displays an error in the red indicator at the bottom of the dialog box saying, "The calculations contain errors." To get a clue as to what is wrong, click on the error message. 

Also, keep in mind that when Tableau says that a calculation is valid, it doesn't mean that the results you get will be correct; it only tells the syntax is correct. To ensure that Tableau calculates the result correctly, put the raw 'ingredients' on the view and calculate the outcome manually. 

In this example, create a quick table showing your orders sales and freshly made AOV measures. Then, divide the same amount by the number of orders to ensure AOV displays the correct answer.  



In the above Image, Tableau calculated the correct answer, i.e., $2,297,201 in sales divided by a total number of sales which is 5,009. That gives $459. The AOV is supposed to be displayed in currency; for that, right-click on the calculated field in the Measure Shelf, hover over "Default properties," and click on "Number format."

Note: Tableau is written in a proprietary language known as VisQL. It is a Linq-type language.  

Aggregating calculated fields in Tableau


Aggregation is an important concept to be kept on top of the mind while creating a calculated field. A calculated field for SUM([Profit]) / SUM([Sales]) is very different from the calculated field for [Profit] / [Sales]. 

However, both these formulas are valid.  

If you do not use the expression for aggregation in the calculated field, Tableau will calculate the equation for every record in the analysis. And then, it will sum up all of the rows together when the calculated field is added to the view. It is crucial to check the calculated field and see the correct results before integrating new measures in the dataset. 

To hit this point, we have made a new measure for Orders and used it in a new calculated field for AOV. Now put the new field to work by answering some business questions: 

What is the AOV of the product subcategories you managed in comparison to everything in the dataset? 

As an explanation, we consider that we manage the Copiers, Machines, and Supplies sub-categories. Now, create a third calculated field, creating a new Sub-Category Segmentation dimension.

In this calculation, we will incorporate IF/THEN Logic for creating segmentation—a segment for sub-categories managing copiers, Machines, and Supplies and another segment for every other sub-category.

Here's what your formula should look like:  



This shows that the dimension member of Sub-Category matches Copier, Machines, or Supplies. We want data classified as "My Sub-Categories." If the dimension member is anything other than these three sub-categories, we want it to be classified as "Other."

As always, we will check this dimension calculation: place the original dimension on the rows shelf, and then check the newly created calculated field in Tableau to ensure proper calculation of dimension members. 



Notice how three sub-categories are classified as "My Sub-categories" in the second column, and everything that lies outside is classified under "Other."

Now answer the business questions by placing the AOV calculated field in the view. Then, slice and dice the measure using the Sub-Category Segment dimension.



This depicts that AOV for the product subcategories managed is way higher than AOV for other sub-categories.

Conclusion


We saw how to create a calculated field in Tableau. And also learned to aggregate this calculated field.

If you are in Marketing or finance, you'd need to see the Profit Margin applied to several analyses. You might also need some charts to give some instant insight. This is when your knowledge of the Tableau calculated field will become fruitful.

Using a Calculated field is when you truly start to take your analysis to the next level, so it's critical to learn the main logical functions that are extremely useful to get the ball rolling.

Sign up for our newsletter to get an update when more such posts go live.

Also, if you have benefitted from this, do share it with your friends and colleagues.

Recommendations

Sankey Diagram: Detailed Guide to Visualizing Data Flow Blog Image

Sankey Diagram: Detailed Guide to Visualizing Data Flow

Learn how to visualize data flow with a detailed guide on creating Sankey diagrams. Explore examples, input data, and tools to build this impactful chart.

Jun 20, 2024
Pie Charts Misuse: Shocking Cases in Data Visualization Practices Blog Image

Pie Charts Misuse: Shocking Cases in Data Visualization Practices

Discover shocking cases of pie chart misuse in data visualization practices. Learn why using pie charts with multiple small slices can be misleading.

Jun 06, 2024
10 graphic design tips to improve your dashboards on Tableau Blog Image

10 graphic design tips to improve your dashboards on Tableau

Improve your dashboards with these 10 graphic design tips and impress everyone with your Tableau skills!

May 21, 2024

You Are Only 60 Days Away From Your Business Intelligence Dream