Create Calculated fields in Tableau

What is calculated fields in Tableau?

Tableau Calculation Fields are user-defined expressions that allow users to create new data fields (or new column) based on the existing data fields in your Tableau data source. Calculated fields in tableau can be used to perform a wide range of operations like mathematical calculations, string manipulation, date manipulation, creating aggregated data, Logical calculation and much more.

What is the use of calculated fields in tableau

You calculate field in your data source without impacting the original data source, this addition calculated fields use to strong your analysis and visualization in Tableau dashboards. this calculated fields in tableau will help you to make you imagination into the reality output.

You can use calculated filed for many reasons and benefits as mentioned below

• Aggregate your data
• Make your own calculation
• Enhance your visualization
• Segment your data
• Use calculated filed for filters
• Changes your data types in the data source

Tableau calculated field Syntax

The syntax for creating a Tableau Calculated Fields depends on the type of calculation you want to perform. Here are some examples:

• Mathematical Calculation: To create a new field that calculates the sum of two existing fields, you can use the following syntax:

[Field 1] + [Field 2]

• String Manipulation: To create a new field that concatenates two existing fields, you can use the following syntax:

[Field 1] + " " + [Field 2]

• Date Manipulation: To create a new field that calculates the number of days between two dates, you can use the following syntax:

DATEDIFF('day', [Start Date], [End Date])

Key features of Tableau calculated fields include

• Flexibility: Tableau Calculation Fields provide a great deal of flexibility in terms of what you can do with your data. You can create custom calculations that are tailored to your specific needs and use cases.

• Ease of Use: Tableau Calculation Field interface is user-friendly, making it easy for users of all skill levels to create and use calculated fields. the box of calculated field provide colorful syntax which help users to identify keywords, and variables.

• Speed: Calculation fields can be created and computed quickly, allowing users to iterate and experiment with their data analysis without delay.

• Compatibility: Tableau Calculation Fields can be used with a wide range of data sources, including spreadsheets, databases, and cloud-based data storage solutions.

• Integration: Calculation Fields can be easily integrated into Tableau dashboards, allowing users to create custom visualizations that incorporate their calculated fields.

Types of calculated fields

There are three major types of calculated fields in tableau

1. Basic Calculation: basic calculated fields in tableau are those field which is calculated at row level or at visualization level for your dashboard (its also calculate aggregated level). Its create new fields in your data source example, string function to concatenate name, Sum function to add two values etc…
2. Table Calculation: table calculation exist at the level of visualization, meaning its calculate data at the local level of visualization. its compute what is currently at visualization level, its don’t consider any dimension filtered out in the viz. example calculate moving average at table(across) method.
3. Level of details expression (LOD): LOD is the same calculation as the basic calculation the difference is that level of details expression (LOD) provide more control at the granular level of data. Example, calculate sale for FIXED customer in the viz. you can use Keyword FIXED, INCLUDE and EXCLUDE to perform the LOD calculation.

Must read: Lookup Function in Tableau

Create calculated field in Tableau

Below are the steps to create calculated field in Tableau

1. Insert new sheet in the Tableau

2. On the data panel, click on the small triangle icon as shown below image > then click on “Create Calculated field“

3. New calculation window box will be open

4. Now give the name of calculated field, lets take name as ” [calculated_Sales]”

5. Now write the calculation formula in that box, lets take example of sum of sales formula as shown below image

6. Now click on Ok button

7. After completion you will see the [calculated_Sales] new field in the data panel with “=” sign in the beginning as shown below image

Note:

All calculated fields in tableau always start with “=” sign

Below are 5 examples of calculated fields in Tableau tool with syntax:

Example #1: Calculate percentage of Total in Tableau

Many time we need to calculate how much is the percentage of each rows with the total or you can say the share of percentage for the category. To calculate a Percentage of total in tableau you need to create the calculated fields and then you can drag that calculated filed into your Tableau table.

Here we will use the SUM() function in calculated filed, which will calculate the aggregated sum of row and Total sum of the row and it will divide row total with the sum total.

(Sum([Sales])/TOTAL(sum([Sales])))

This formula calculates the percentage of sales out of the total sales.

In below output of image, you can see first column is the sales of [Items type] and Second column is the Percentage of Total

Calculated filed is the Table (down) method calculation see in below image

Example #2: Calculate running total in Tableau

To calculate a running total in tableau, you need to use Running_Sum function which will automatically calculate running total for particular measures. To calculate a running total, you can use the following syntax:

RUNNING_SUM(SUM([Sales]))

This formula calculates the running total of sales. you can use this calculated filed in table or in visualization chart or graph.

Example #3: Calculate the difference between two dates in Tableau

if you want to calculate the difference between two Dates then you need to use DATEDIFF function in tableau to calculate the difference. This formula calculates the number of months between the start and end date.

Here we will take difference of [order date] and [ship date] and calculate number of Months between these two days, you can use the following syntax:

DATEDIFF('month',[Order Date], [Ship Date])

This formula calculates the number of Months between two dates as shown below image as output

Example #4: Concatenate in Tableau

Suppose you have two fields and you want to concatenate these two fields in tableau. So for that to concatenate in Tableau you can use the following syntax.

Below we are concatenating region and country so that we can know which [country] fall under which [Region] and also use bracket to separate the region inside the bracket to have more clarity of visualization of country and region.

[Country] + " " + "(" + [Region] + ")"

Below is the output of concatenate in table for country and region

Example #5: Convert string to Number in tableau

This is very common and simple thing you need to convert string to number because in your data source sometime amount contains the currency symbol and which make the amount into string data type.

Convert string to number in tableau, you can use the following syntax:

FLOAT(REPLACE([Amount], "$", ""))

This formula converts the “Amount” field, which contains dollar signs, to a floating-point number by removing the dollar sign and converting the string to a number.