In this blog you will learn about Advanced calculated fields in Tableau which are powerful tools that enable users to perform complex data manipulation and create custom metrics, helping to gain deeper insights from data. These calculations go beyond basic arithmetic operations and allow users to apply logical operations, date functions, string manipulation, and aggregate calculations. Understanding and using advanced calculated fields can significantly enhance Tableau’s analytical capabilities.
Types of Calculated Fields in Tableau
- Basic Expression
- Level of Detail Expression
- Table Calculations
Basic Expression:
Basic expressions in Tableau allow you to create calculations using arithmetic operations, logical comparisons, and functions (such as string, date, and number functions). These are straightforward calculations that work on a row-by-row basis in your data source.
Use Case
- Simple mathematical operations: Calculating profit by subtracting costs from sales.
- Logical conditions: Classifying sales into “high”, “medium”, and “low” based on thresholds.
- String manipulation: Combining first and last names into a full name field.
Follow the steps to perform simple mathematical operation: Calculating Profit
Step 1: Open Tableau
Step 2: Click on Text file to connect with Tableau
Step 3: Browse csv file (or other file format as your need) and click on Open
Step 4: Click on Sheet 1 (Worksheet) to make visual
Step 5: Drag-n-Drop Product Category to Rows and Measure Names to Columns
Step 6: Drag-n-Drop Measure Values over Text
Step 7: Click on Analysis tab and select Create Calculated Field…
Step 8: Give a name for calculated field; Type following formula and click on OK
Note:
- Here, We’ve used (-) operator to find profit value from Sales and Cost
Step 9: Drag-n-Drop Profit measure in Measure Values Shelf
Level of Detail (LOD) Expression:
This allows you to control the granularity of the calculations being performed. They can calculate values at different levels of detail than what is currently displayed in the view. There are three main types of LOD expressions: Fixed, Include, and Exclude.
Use Case
- Overall calculation: Calculating total sales per region, regardless of the product categories shown in the view.
- Custom grouping: Calculating average sales per customer across different product categories.
- Data partitioning: Grouping data based on certain levels such as city or region while ignoring other dimensions in the view.
Follow the steps to: Calculate the total sales for each region, ignoring the product category
Step 1: Click on Analysis tab and select Create Calculated Field…
Step 2: Give a name for calculated field; Type following formula and click on OK
Step 3: Drag-n-Drop Total Sales Per Region measure in Measure Values Shelf
Table Calculations:
Table calculations are performed based on the data currently displayed in your view, rather than the entire dataset. They allow you to perform operations such as running totals, moving averages, and percentages of a total.
Use Case
- Running total: Calculating cumulative sales over time.
- Moving average: Smoothing out data trends by averaging sales over a specific period.
- Percentage of total: Showing the contribution of each segment to the overall total.
Follow the steps to: Calculate Running Total of Sales
Step 1: Click on Analysis tab and select Create Calculated Field…
Step 2: Give a name for calculated field; Type following formula and click on OK
Step 3: Drag-n-Drop Running Total of Sales measure in Measure Values Shelf
Dataset:
Order ID | Customer ID | Region | Product Category | Order Date | Sales | Cost |
101 | C001 | North | Electronics | 15-01-2023 | 2500 | 1500 |
102 | C002 | South | Furniture | 20-02-2023 | 1500 | 800 |
103 | C003 | East | Electronics | 12-03-2023 | 3000 | 1800 |
104 | C001 | North | Furniture | 01-04-2023 | 1800 | 1000 |
105 | C004 | West | Office Supplies | 10-05-2023 | 2200 | 1300 |
Conclusion:
Advanced calculated fields in Tableau—whether using basic expressions, LOD expressions, or table calculations—provide powerful tools for transforming and analyzing data. Basic expressions handle fundamental calculations, LOD expressions control the nuances of calculations, and table calculations provide flexibility within visualizations. Mastering these leads to deeper insights and more dynamic, customizable dashboards, allowing users to unlock the full analytical potential of Tableau.
If you like the article and would like to support me, make sure to:
- 👏 Like for this article and subscribe to our newsletter
- 📰 View more content on my DataSpoof website
- 🔔 Follow Me: LinkedIn| Youtube | Instagram | Twitter
