Perhaps Tableau's most powerful feature is its ability to allow authors to create new data from existing data through calculated fields. Calculated fields can be used to create new dimensions, such as segments, or new measures, such as ratios. They can also be used with any data type, a large number of functions and aggregations, as well as logical operators, making the computed results virtually limitless. This post introduces calculated fields by showing you how to create two new measures and one new dimension in the Sample - Superstore dataset.
Why use calculated fields?
You may be wondering what's with all the praise about calculated fields? After all, we've also suggested that it's best toprepare your data as best you can before it reaches Tableau, and that data should contain all required fields for your analysis. Right?
There are many reasons to use the calculated field functionality in Tableau. Here are a few:
- To instantly segment your data in new ways
- To prove a concept, such as a new dimension or measure, before it becomes a permanent field in the underlying data
- To filter out unwanted results for better analysis
- To take advantage of the power of parameters and put the choice in the hands of your end users
- To calculate ratios over many different variables in Tableau, saving valuable database processing and storage resources
To bring the concept of calculated fields to life, let's pretend we're working with the Sample – Superstore dataset and want to evaluate the Average Order Value (AOV) for the product subcategories we manage compared to the rest of the business. AOV is defined as the total turnover divided by the number of orders. Although this dataset has Order ID as its dimension, it does not have the number of orders as its measure, which is the denominator for the AOV calculation.
To isolate the number of orders, let's first create a calculated field to count the number of orders. The formula to count the individual number of orders is COUNTD of the Order ID dimension. To start a calculated field you can:
- Click the down arrow in the top right corner of the Dimensions Shelf and choose "Create Calculated Field..."
- Right-click on an empty space in the left sidebar and choose "Create Calculated Field..."
- Click on Analysis in the top navigation and choose "Create Calculated Field..."
- Right-click on any 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, since we know we need the Order ID as part of the calculated field, we will launch the calculated field by right-clicking the Order ID dimension on the Dimension Shelf, hovering over “Create” and click “Calculated field…”. This opens a new dialog where we can enter the formula for my calculated field:
On the right side of the calculated fields dialog box is a definition of the expression COUNTD. Whenever you see a blue color coding when creating a calculated field, you can click on the blue colored word to get a definition and preview of that specific function; this is a great way to learn syntax. After clicking the OK button, you will see a new Orders unit of measure appear on the Measures shelf - a brand new unit of measure that we can now use throughout the workbook!
Now that we have our Orders measure, which is the denominator of the AOV calculation we're looking for, we can create another calculated field to calculate total sales revenue divided by the number of orders. To start this calculated field, right-click on an empty space in the left sidebar and choose "Create Calculated Field...". We are presented with a blank calculated fields dialog where we can enter the formula for AOV:
Note that this time the Sales measure is aggregated as SUM, but Orders doesn't seem to have any aggregation. That's because Orders already has an aggregation of COUNTD in the underlying calculated field 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 displays a red indicator at the bottom of the dialog that says "The calculation contains errors". You can click on the error message to get an idea of what might be wrong with your calculation.
Also remember that even if Tableau says a calculation is valid, that's in terms of the syntax, not the result you're looking for. To check the quality of a calculated field and ensure that the result is calculated correctly, we like to put the raw 'ingredients' in the view and calculate the answer manually. In the case of this tutorial, we would create a quick table of orders, sales, and the newly created AOV metric. We would then divide the sales amount by the number of orders to ensure that the correct answer is shown as AOV.
Based on the image above, it looks like Tableau calculated the correct answer: $2,297,201 in total sales divided by 5,009 total orders equals an average order value of $459. Calculated fields are not number formatted by default. In the case of AOV, which should be displayed as currency, this can be changed by right-clicking on the calculated field on the Measures shelf, hovering over "Default properties" and clicking on "Number format...".
More about merging calculated fields
Aggregation, introduced in the post,5 Ways to Create a Bar Chart / An Introduction to Aggregation, is an important concept to consider when creating calculated fields. A calculated field for SUM([Profit]) / SUM([Sales]) gives you a very different answer than [Profit] / [Sales], even though both formulas are valid. If you don't specify the aggregation within the calculated field, Tableau calculates the equation for each record in your analysis and then aggregates the answers for all rows together when the calculated field is added to the view. It is critical to quality check the calculated fields and make sure you see the expected results before integrating the new measurements into your work.
So far, we've created a new measure for Orders and used the Orders metric in 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 subcategories we manage compared to everything else? For this illustration, we will pretend to manage the Copiers, Machines, and Supplies subcategories. To answer this question, let's create a third calculated field, this time with a new subcategory segmentation dimension.
This calculation is slightly different because we will be using IF/THEN logic to create a segmentation: one segment for the subcategories we manage (copiers, machines and supplies) and the other segment for every other subcategory. The formula looks like this:
All this calculation says is that if the Subcategory dimension member matches copiers, machines, or supplies, we want the data to be classified as "My Subcategories". If the dimension member is anything other than these three, we want the data to be classified as "Other". To check the quality of a dimension calculation like this, we first put the original dimension on the row shelf, followed by the newly created calculated field to check that the dimension members are calculated correctly:
As you can see, my three subcategories are classified as "My Subcategories" in the second column, and everything else is classified as "Other".
We are now ready to answer our business question by placing the calculated AOV field in the view and then split and dice the measure based on the Subcategory Segment dimension:
The answer clearly shows that the AOV for the product subcategories we manage is much higher than the AOV for all other subcategories – maybe it's time we asked for a bonus!
Data Visualization Consulting Services
Related blog posts about data visualization
Inaugural VizJam takes place on the Billboard Hot 100
Tableau visionary Sean Miller joins Evolytics as Associate Director of Data Visualization
Is Alteryx worth the investment?
Sign up to receive analytical tips and insights delivered straight to your inbox.
In a worksheet in Tableau, select Analysis > Create Calculated Field. In the Calculation Editor that opens, give the calculated field a name. In this example, the calculated field is called Profit Ratio.What are the three main types of calculations in Tableau? ›
- Row Level calculations: These calculations are performed for every row of underlying data. ...
- Aggregate Level calculations: These calculations are performed at an aggregate level.
Table Calculations (including the Quick Table Calculations) live in our Tableau View. They are created in the view and stay there, locally in our worksheet. Calculated Fields are created on a data level and appear as a separate column in the data source.How many calculated fields are there in Tableau? ›
There are three main types of calculations Tableau provides in Tableau calculated fields: Basic expressions: Basic operations transform your data at row level or at the visualization level of detail (an aggregate calculation).Does Tableau use SQL for calculated fields? ›
Tableau calculates queries the same as SQL. Table calculations are a bit different and I prefer to leave those aside at least until we tie out the results to those from SQL. Fortunately the SQL is straight-forward, so we can verify each step.What is the purpose of calculated field? ›
A calculated field is a formula that performs some action on one or more other fields in your data source. Calculated fields can perform arithmetic and math, manipulate text, date, and geographic information, and use branching logic to evaluate your data and return different results.What are the 7 data types in Tableau? ›
- String values.
- Number/Integer values.
- Date values.
- Date & Time values.
- Boolean values.
- Geographic values.
- Cluster or mixed values.
In this article, we discussed different data types in tableau: string, numerical, date, date & time, Boolean, geographical, and mixed data type.What is the difference between total and sum in Tableau? ›
What is the difference between SUM and TOTAL functions in Tableau? SUM – is an aggregate function used to sum all the values in an expression. Note, SUM can be used with numeric fields ONLY. TOTAL – is a table calculation function that returns the total of a given expression.Which one is a reason for creating a calculated field Tableau? ›
- To use different filters to remove unwanted results for better analysis.
- To segment data in new ways.
- To calculate ratios across different variables in Tableau, saving database processing and storage resources.
- To convert the data type of a field, such as converting string to date.
- The Filters Are Not Enough. ...
- Segment Data. ...
- Create Basic Expressions. ...
- Create a Simple Field. ...
- Calculate Field If-Then Statements. ...
- Calculate Field Based On Group By. ...
- Create Calculated Field to Filter. ...
- Calculated Tableau Bins.
If your underlying data doesn't include all of the fields you need to answer your questions, you can create new fields in Tableau using calculations and then save them as part of your data source. These fields are called calculated fields.What Cannot be used in a calculated field? ›
The number 12 cannot be used in a calculated field as it is a constant value and...What is the difference between a worksheet and a dashboard in Tableau? ›
– Worksheets contain a single view along with shelves, cards, legends, and the Data and Analytics panes in its side bar. – Dashboards are a collection of views from multiple worksheets. The Dashboard and Layout panes are available in its side bar.What are bins in Tableau? ›
What are Tableau Bins? Tableau Bins are equal-sized containers that store data values that correspond to or fit within the Bin Size. Tableau Bins divide a set of data into groups of equal intervals or sizes, resulting in a systematic distribution of data. Data from any discrete field can be used to create Tableau Bins.Which database is best for Tableau? ›
- Quickbase. 4.5. (274) No-code collaborative work and project management platform. ...
- kintone. 4.7. (143) Agile, No-code Business Application Platform. ...
- MongoDB. 4.7. (443) ...
- Claris FileMaker. 4.3. (194) ...
- MariaDB. 4.7. (70) ...
- Snowflake. 4.6. (71) ...
- Improvado. 4.6. (22) ...
- SolarWinds SQL Sentry. 4.7. (16)
SQL is not essential if you want to learn Tableau and work with it, but it is required if you want to obtain a job since you will need to construct Tableau dashboards using data after you acquire a job and start working on a project.How many fields can Tableau handle? ›
Users can build wide tables in Tableau with up to 50 fields on the Rows shelf (i.e. have up to 50 columns in their table).What is an example of a calculated field? ›
For example, the calculated field formula =Sales * 1.2 multiplies the sum of the sales for each type and region by 1.2; it does not multiply each individual sale by 1.2 and then sum the multiplied amounts.What is the difference between a calculated column and a measure? ›
a calculated column belongs to a single table, while a measure belongs to the whole data model. A calculated column is evaluated in a row context (row by row, like in an excel table), while a measure is evaluated in the filter context.
Calculated fields belong in queries, not tables.
Calculated columns are part of life on a spreadsheet, but do not belong in a database table. Never store a value that is dependent on other fields: it's a basic rule of normalization.
As a data analysis tool, Tableau classifies every piece of data into one of the four categories namely - String, Number, Boolean and datetime.What are the 4 main data types? ›
4 Types of Data: Nominal, Ordinal, Discrete, Continuous.What are the two types of sets in Tableau? ›
There are two types of sets: dynamic sets and fixed sets. The members of a dynamic set change when the underlying data changes. Dynamic sets can only be based on a single dimension.What are 3 different data visualizations options in Tableau? ›
- Bar Chart. Bar charts are one of the most common data visualizations. ...
- Line Chart. The line chart, or line graph, connects several distinct data points, presenting them as one continuous evolution. ...
- Pie Chart. ...
- Maps. ...
- Density Maps. ...
- Scatter Plot. ...
- Gantt Chart. ...
- Bubble Chart.
Finally, coming on the types of Data Sets, we define them into three categories namely, Record Data, Graph-based Data, and Ordered Data.How many tables can be joined in Tableau? ›
In Tableau, Join is a condition used in Query to add two or more tables. We can add a maximum of 32 tables in Tableau.What are the functions in Tableau? ›
- Number Functions.
- String Functions.
- Date Functions.
- Type Conversion.
- Logical Functions.
- Aggregate Functions.
- Pass-Through Functions (RAWSQL)
- User Functions.
LOOKUP. LOOKUP() : LOOKUP() is an important table calculation function. It returns the value of the specified expression in a “target row”. “target row” is specified as a relative position of the row from the current row.How do I add two values in Tableau? ›
- sum = [Sales] + [Profit]
- sum = SUM([Sales]+[Profit])
- This follows for both numbers, decimal numbers, %'s.
General Rule: Avoid using a calculated field multiple times in another calculation. Referencing the same calculated field multiple times within another calculation will result in performance issues.What is the most important function in Tableau? ›
Type Conversion. It is the most used function in Tableau, and I use it to convert strings to date, Integers to strings, strings to floats, and date parsing. Here is the list of type conversion functions: DATE(expression)Why prefer Tableau over Excel? ›
Tableau can access and display unlimited amounts of data. We have customers that analyze 10's of billions of rows of data. Create pivot tables with unlimited number of rows, columns, members, and cells. Excel has many limitations in all these areas that confound even simple analysis.What are the advantages of calculated fields? ›
Calculated fields lend to more flexibility and efficiency in your analyses. Power multiple visual analyses with one query - You can now create multiple calculations on top of one Helix-powered query.Why do we pivot data in Tableau? ›
By pivoting the data you can create rows for each month and year and individual columns for sales, taxes and totals so that Tableau can more easily interpret this data for analysis.What are two main groups that all fields are broken up to in Tableau? ›
When you connect to a new data source, Tableau assigns each field in the data source as dimension or measure in the Data pane, depending on the type of data the field contains.What is the number one benefit of Tableau? ›
Tableau can handle large amounts of data:
Tableau can handle millions of rows of data with ease. Different types of visualization can be created with a large amount of data without impacting the performance of the dashboards.
Tableau is a popular visualization tool that most organizations and IT companies use to evaluate their data. Tableau project help you to connect with several data sources, analyze data, and visualize it to get superior insights.What are the advantages of calculated column vs calculated measures? ›
The calculated column has knowledge of the current row. By contrast, measures implicitly do not have a row context. This is because, by default, they work at the aggregate level. So, you cannot refer to columns directly in a DAX measure, you will get an error because no row context exists.How do Calculations work in Tableau? ›
Calculations allow you to create new data from data that already exists in your data source, as well as perform computations on your data. This allows you to perform complex analyzes and add fields to your data source on your own and on the fly.
Create the calculated fields
These steps use the Superstore sample to build the calculated fields that will take advantage of your parameters. In the Data pane, click the drop-down arrow in the upper right corner and select Create Calculated Field. In the Calculated Field dialog box, for Name, type Column 1 Category.
A calculated field is a field that uses existing database fields and applies additional logic — it allows you to create new data from your existing data. A calculated field either: performs some calculation on database fields to create a value that is not directly stored in the database or.What is an example of a table calculation in Tableau? ›
Common examples of table calculations include running sum, moving average, and percent of total. Calculations are computed over local data (post-filtered data) within Tableau. The important concepts to keep in mind are: Table calculation math will be based only on dimensions (granularity) within the view.What is the difference between sets and parameters in Tableau? ›
Sets enable you to visualize groups which match one or more criteria. They are even more powerful in combination with parameters which allow you to dynamically change the criteria for the sets.What is the difference between filter and parameter in Tableau? ›
Filters are different from parameters. Filters are created on the worksheet level and is specific to a data source whereas parameters are reused across entire workbook and thus it is not specific to a data source.What are examples of calculated fields? ›
For example, the calculated field formula =Sales * 1.2 multiplies the sum of the sales for each type and region by 1.2; it does not multiply each individual sale by 1.2 and then sum the multiplied amounts.What is the difference between query field and calculated field? ›
Queries are a better alternative to Calculated fields because they do not store the value in a table. Queries are helpful in automating data management tasks and revision of changes. Queries can be created using the Query Wizard or in Deign view.What is a calculated field also known as? ›
In a pivot table, calculated fields and calculated items are custom formulas.