What is Tableau Calculated Field?
Tableau calculated field enables you to create new fields in your dataset using the existing data in your data source. It allows you flexibility in creating new fields that can be further used in Tableau calculation or reporting by defining the calculation methodology you can control. Once the calculated field is created, it is saved to your dataset and can be used in a way similar to any other existing data fields. Tableau calculated fields provide multiple capabilities and benefits such as data segmentation, data type conversion, data aggregation, choice of filters, and calculation of any ratios for your reporting purpose.
Tableau calculated fields can be created using three key types of calculations, which include:
- Basic calculations to transform values at the data source level of details (row-level) or aggregated calculations.
- Level of Detail expressions to calculate values at data source level and aggregate but with added granularity.
- Table calculations to transform values at the data visualization level.
A sample Tableau calculated field syntax looks like the one provided below.
Table of contents
Key Takeaways
- Tableau calculated field enables you to create new fields in your dataset using the existing data in your data source.
- With a calculated field, you can perform data segmentation, data type conversion, data aggregation, choice of filters, and Tableau calculated field percentage for any ratios for your reporting purpose.
- You can create a calculated field either by right-clicking on the Data pane or navigating Analysis – Create Calculated Field.
- You can use the Tableau calculated field functions to create calculated fields depending on the type of calculations you want to perform.
How to create a calculated field in Tableau?
To create a calculated field in Tableau, you may follow the instructions provided below:
Step 1: Connect your dataset to the Tableau interface.
Step 2: Navigate to a new worksheet in Tableau. There are two options you can choose from:
- Right-click on the Data pane and select Create Calculated Field…
- Navigate to Analysis – Create Calculated Field…
Step 3: In the Calculation Editor, specify the name of the calculated field.
Step 4: Specify the logic for the calculated field. Once completed, click OK to save the changes.
Tableau will create the calculated field saved to your dataset, as shown below.
Similarly, you can also create a calculated date field using field date format. For example, you can use the below DATEDIFF function to create a calculated field to calculate the date difference between two dates.
DATEDIFF(‘week’,#2024-05-26#, #2024-05-27#, ‘monday’) returns 1.
In the next section, we will look at a few examples to understand a bit more about the calculated fields in Tableau.
Examples
In this section, we will demonstrate different scenarios where you can use the calculated field in Tableau.
Example #1 – Calculate Field If-Then Statements
In this example, we will demonstrate creating a calculated field using If-Then statements in Tableau public using the Swiss Bills dataset, which contains grocery pricing data collected over two years in Switzerland, covering 35 different product categories.
To create a calculated field using If-Then statements in Tableau public, follow the step-by-step guide outlined below:
Step 1: Connect the Swiss Bills to the Tableau.
Step 2: In a new worksheet tab, right-click on the Data pane and select Create Calculated Field.
Step 3: In the calculation editor dialog box, specify the name of the calculated field and enter the If-Then statements for the field.
Here, we have created a field Price Category based on the price range of the products.
Once complete, click on OK to save the changes. Tableau will create a calculated field visible in the Data pane.
Step 4: Create a visualization by dragging and dropping the attributes to the Tableau view.
Example #2 – Calculate Field Based On Group By
In this example, we will demo creating a calculated field based on group by in Tableau public using the Sales Dataset. The Sales Dataset contains the Purchased items list of the United Nations States such as Sales Price, Quantity of purchase, Profit, Delivery date, salesperson, States, Order Status, etc.
To create a calculated field based on group by in Tableau public, follow the instructions below:
Step 1: Import the Sales Dataset in Tableau using File – Open.
Step 2: In a new worksheet, navigate to Analysis – Create Calculated Field…
Step 3: In the calculation editor, specify the name and logic based on group by. Here, we have created a category of products based on their revenues. Click on OK to save the changes.
Step 4: In the view, drag and drop the Product to the Rows shelf and Revenue to the Columns shelf.
Step 5: Drag and drop the Product_Value_Category field to the Rows shelf.
Step 6: Sort the field in descending order.
Step 7: Change the visual to the horizontal bars, as shown below. Now, your Tableau chart is ready for view.
Example #3 – Calculated Field to Filter Order Of Operations
In this example, we have created a calculated field to filter the order of operations in Tableau using the MultiSalesStore dataset. The MultiSalesStore dataset contains essential details such as Order Date, Shipping Date, Shipping Mode, Postal Code, Region, Product Reference, Category, Sub-Category, Sales, Quantity, Profit, and State.
To create a calculated field to filter the order of operations in Tableau public, follow the steps below:
Step 1: From the Tableau public interface, connect the MultiSalesStore dataset. The imported dataset is now available in the Data Source tab.
Step 2: Navigate to the Analysis – Create Calculated Field…
Step 3: In the calculation editor window that opens, specify the logic to perform the computation using Tableau calculated field functions.
Step 4: Drag and drop the fields, i.e., Region to the Columns shelf and Category and Sub Category to the Rows shelf. Similarly, drag the Adjusted Profit to the view.
Step 5 : Drag the Adjusted Profit field to the Filters pane. Define the filter criteria, as shown below.
Now, your Tableau visualization is ready for view.
Example #4 – Calculate Exclude Field
In this example, we will demonstrate how to create a calculated field using the Exclude which is one of the Tableau calculated field functions. For this demonstration, we will use the Sales Dataset that we have leveraged for Example 2.
To create a calculated field using the Exclude function, follow the instructions below:
Step 1: Drag and drop the fields Product and Age Group to the Rows shelf and Revenue to the Columns shelf.
Step 2: Sort the visual by clicking on the Sort icon as shown below.
Step 3: Drag the Revenue field to the Label on the Marks card.
Step 4: Drag the Product to Color on the Marks card.
Step 5: In the Calculation editor, specify the logic using the Tableau calculated field functions, i.e., EXCLUDE.
Step 6: Drag the newly calculated field to the Color on the Marks card. As you can notice, adding the ExcludeAgeGroup field to the Color shades the view to display total revenue without the Age group.
Example #5 – Conditional Calculated Field
In this example, we will demonstrate creating a conditional calculated field using the Tableau calculated field functions, i.e., IIF. For this demonstration, we will use the stateness-index new dataset that contains the stateness index based on the expert estimates and index by the Bertelsmann Transformation Index that combines information on the extent to which the state has the monopoly on the use of force, etc.
To create a conditional calculated field using the IIF function in Tableau, follow the steps below:
Step 1: Connect to the stateness-index new dataset from your Tableau interface using File – Open. The imported data can be viewed in the Data Source tab.
Step 2: Navigate to Analysis – Create Calculated Field in a new worksheet.
Step 3: In the calculation editor screen, rename the field to Stateness Classification. Specify the logic to classify the data based on the Stateness index value.
Click on OK to create the field.
Tableau will save the calculated field to the dataset in the Data pane.
Step 4: Drag the fields to the view to create a data visualization in Tableau. As you can see, the Stateness Classification is now reflecting the values as per the defined values.
Example #6 – Bins from Calculated Fields
In this example, we will demonstrate how to create bins from calculated fields using the Oil Production Dataset. This dataset contains insights into the oil production across 36 countries from 2021 to 2023.
Step 1: Import the Oil Production Dataset into the Tableau interface.
Step 2: Drag Year to the Columns shelf and Product to the Rows shelf. Drag the Value to the view.
Step 3: Filter the Product by dragging it into the Filters pane. Here, we have filtered out the Total values.
Step 4: Drag the Flow field to the Rows shelf.
Step 5: Navigate to the Analysis – Create Calculated Field…
Step 6: In the calculation editor, specify the logic for the calculated field.
Step 7: Now, right-click on the calculated field and select Create – Bins…
Now, your bin is ready to be used in Tableau view.
Example #7 – Sort By Calculated Field
In this example, we will create a calculated field and then sort the Tableau view on this calculated field using The best wine producers in the world dataset, which unveils the titans of the wine world, showcasing the top 20 countries that quench our thirst with their delectable vintages.
To demo the sort by calculated field in Tableau, follow the steps below:
Step 1: Connect to the dataset from the Tableau interface.
Step 2: Navigate to Analysis – Create Calculated Field…
Step 3: Define the calculated field in the calculation editor.
Step 4: Drag the calculated field to the view.
Step 5: Now click on the Sort icon next to the calculated field. Your Tableau visualization is ready.
Importance Things to Note
- Always ensure that you follow the syntax for creating a calculated field for accurate calculations and processing.
- Calculated fields don’t alter or modify the original data; they are created as new fields that you can use for calculation or visualization purposes.
- You can create a calculated field depending on the type of calculations you want to perform.
- Consider the data type conversions if needed to ensure that the calculated field returns accurate results.
Frequently Asked Questions (FAQs)
To concatenate in the Tableau calculated field, you can use the + operator in Tableau.
For example, here we have created a calculated field Order Description by concatenating Category and Product Name.
To get the current date in the Tableau calculated field, you can use a date function TODAY().
To use the contains in the Tableau calculated field, follow the steps below:
Step 1: Navigate to Analysis – Create Calculated Field.
Step 2: In the Calculation Editor screen, enter the formula with the contains function. Click on OK to save the changes.
Step 3: Use the calculated field in your view to create Tableau visuals.
FIXED is a level of detail expressions that calculates a value using defined dimensions without referencing any dimensions in the Tableau view.
For example, the expression {FIXED [Region]: SUM([Sales])} creates a calculated field that calculates the total sales per region.
Download Template
This article must be helpful to understand the Tableau Calculated Field, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is the Tableau Calculated Field. We explain how to create a calculated field with syntax, examples, and points to remember. You can learn more from the following articles –
Leave a Reply