🔥 PHASE 2 · LEVEL 3
🟡 Intermediate
MODULE 06
Calculated Fields
Welcome to Phase 2 — intermediate Tableau. Calculated fields are one of Tableau's most powerful features: they let you create entirely new fields by writing formulas directly inside Tableau. No more pre-processing in Excel, no SQL views needed. You define the logic, Tableau computes it row-by-row or as an aggregation. This is where good analysts become great ones.
Module 6 of 25+ · Phase 2 Progress
28%
🧮 Section 1
What Are Calculated Fields?
A Calculated Field is a new field you create within Tableau by writing a formula. It appears in the Data pane alongside your original fields and can be used just like any native field — dragged to shelves, used in filters, and placed in tooltips. The formula is evaluated at query time, not stored in your source data.
Creating a Calculated Field
Three ways to create one: (1) Analysis menu → Create Calculated Field, (2) Right-click in the Data pane → Create Calculated Field, (3) Keyboard shortcut Ctrl+L (the fastest). The calculation editor opens — type a name for your field, then write the formula in the editor below. Tableau provides live syntax highlighting and auto-complete. A green checkmark at the bottom means the formula is valid.
Types of Calculated Fields
| Type | Evaluated | Returns | Example |
|---|---|---|---|
| Row-Level Calc | For each row of data | A dimension or measure | [Sales] * (1 - [Discount]) |
| Aggregate Calc | After aggregation | A measure | SUM([Profit]) / SUM([Sales]) |
| Table Calculation | On the aggregated result | Running totals, ranks | RUNNING_SUM(SUM([Sales])) |
| LOD Expression | At a specified level of detail | A measure | {FIXED [Region]: SUM([Sales])} |
💡
Row-Level vs Aggregate — Critical Distinction
A row-level formula like
[Sales] - [Cost] computes a value for every single row of data. An aggregate formula like SUM([Profit]) / SUM([Sales]) computes after grouping. Never mix the two without understanding the difference: [Profit] / [Sales] (row-level) ≠ SUM([Profit]) / SUM([Sales]) (aggregate). The aggregate version is almost always what you want for KPI ratios — the row-level version can produce wildly incorrect averages.🔤 Section 2
String Functions
String functions manipulate text fields — cleaning, extracting, combining, and transforming string data. Essential for fixing messy product names, extracting domain from email addresses, or normalizing geographic data.
UPPER / LOWER
UPPER([Customer Name])
Converts to ALL CAPS or all lowercase. Use for case-insensitive comparisons or consistent display.
LEN
LEN([Product Name])
Returns the character count of a string. Useful for data quality checks — flagging suspiciously short or long values.
CONTAINS
CONTAINS([Email], "@gmail")
Returns TRUE if the string contains the substring. Use for filtering or segmenting — e.g., find all Gmail customers.
LEFT / RIGHT
LEFT([Order ID], 4)
Extracts N characters from the left or right of a string. Useful for extracting codes, prefixes, or years from ID fields.
MID
MID([Code], 3, 5)
Extracts a substring starting at position 3, for 5 characters. Use when the piece you need is in the middle of a string.
SPLIT
SPLIT([Name], " ", 1)
Splits a string by a delimiter and returns the Nth part. SPLIT("John Smith", " ", 1) returns "John". Great for separating first/last names.
REPLACE
REPLACE([Phone], "-", "")
Replaces all occurrences of a substring. Use to remove unwanted characters like dashes in phone numbers or spaces in IDs.
TRIM
TRIM([City])
Removes leading and trailing spaces. Essential when imported data has invisible whitespace causing wrong join results.
Practical Example: Extracting Customer Domain
// Extract email domain from customer email field
// Input: "john.doe@gmail.com" → Output: "gmail.com"
MID([Customer Email],
FIND([Customer Email], "@") + 1,
LEN([Customer Email])
)
📅 Section 3
Date Functions
Date functions are critical for time-based analysis — calculating order-to-ship time, building year-over-year comparisons, grouping by fiscal quarter, and computing customer age. Tableau's date functions work with Date and DateTime fields.
| Function | Syntax | Returns | Example Output |
|---|---|---|---|
DATEPART | DATEPART('month', [Order Date]) | Integer (1–12) | 3 (for March) |
DATENAME | DATENAME('month', [Order Date]) | String name | "March" |
DATEDIFF | DATEDIFF('day', [Order Date], [Ship Date]) | Integer (units between) | 3 (days to ship) |
DATEADD | DATEADD('month', 3, [Order Date]) | Date (shifted) | 3 months later |
TODAY | TODAY() | Current date | 2026-06-09 |
NOW | NOW() | Current datetime | 2026-06-09 14:32:00 |
DATETRUNC | DATETRUNC('month', [Order Date]) | Date (truncated) | 2024-03-01 (first of month) |
Practical Example: Days to Ship KPI
// Calculate shipping time in days per order
Days to Ship: DATEDIFF('day', [Order Date], [Ship Date])
// Categorize shipping speed
Shipping Speed:
IF [Days to Ship] <= 2 THEN "Fast"
ELSEIF [Days to Ship] <= 5 THEN "Standard"
ELSE "Slow"
END
Building a Year-over-Year Comparison Field
// Current year sales (row-level flag — use with aggregation)
Is Current Year: YEAR([Order Date]) = YEAR(TODAY())
// Current Year Sales (aggregate calculated field)
CY Sales: SUM(IF YEAR([Order Date]) = YEAR(TODAY()) THEN [Sales] END)
// Previous Year Sales
PY Sales: SUM(IF YEAR([Order Date]) = YEAR(TODAY()) - 1 THEN [Sales] END)
🔀 Section 4
Logical Calculations
Logical functions let you apply business rules and create conditional classifications. These are the most commonly used calculated fields in real-world Tableau work — almost every dashboard uses at least one IF statement.
IF / ELSEIF / ELSE
// Customer segment based on total order value
Customer Tier:
IF SUM([Sales]) >= 10000 THEN "Gold"
ELSEIF SUM([Sales]) >= 5000 THEN "Silver"
ELSEIF SUM([Sales]) >= 1000 THEN "Bronze"
ELSE "Standard"
END
IIF — Inline If (Compact)
IIF(condition, true_result, false_result) is a compact version for simple two-outcome conditions:
// Flag profitable orders
Profitable: IIF([Profit] > 0, "Yes", "No")
// Discount flag
Has Discount: IIF([Discount] > 0, "Discounted", "Full Price")
CASE / WHEN — Pattern Matching
CASE is cleaner than IF when matching a single field against many specific values:
// Standardize region names from raw data abbreviations
Region Full Name:
CASE [Region Code]
WHEN "N" THEN "North"
WHEN "S" THEN "South"
WHEN "E" THEN "East"
WHEN "W" THEN "West"
ELSE "Unknown"
END
AND / OR Operators
// High-value, high-discount orders (risk segment)
At Risk: [Sales] > 500 AND [Discount] > 0.3
// Orders needing attention: late OR damaged
Needs Review: [Days to Ship] > 7 OR [Return Status] = "Returned"
📐 Section 5
Aggregation & KPI Metrics
The most valuable calculated fields you'll build are KPI metrics — measures that aggregate data into the key performance indicators that executives and analysts track daily. Tableau's aggregation functions are the building blocks for every KPI dashboard.
Core Aggregation Functions
| Function | Purpose | Example |
|---|---|---|
SUM | Total all values | SUM([Sales]) → total revenue |
AVG | Average of values | AVG([Order Value]) → avg order size |
COUNT | Count rows (including duplicates) | COUNT([Order ID]) → total orders |
COUNTD | Count distinct values (unique) | COUNTD([Customer ID]) → unique customers |
MIN / MAX | Minimum/Maximum value | MIN([Order Date]) → earliest order date |
MEDIAN | Median (middle value) | MEDIAN([Sales]) → median order value |
STDEV | Standard deviation | STDEV([Profit]) → profit variability |
Building Essential KPI Metrics
// Profit Ratio (most important retail KPI)
Profit Ratio: SUM([Profit]) / SUM([Sales])
// Format as % in Format → Numbers → Percentage
// Average Order Value (AOV)
AOV: SUM([Sales]) / COUNTD([Order ID])
// Customer Lifetime Value proxy
CLV: SUM([Sales]) / COUNTD([Customer ID])
// Discount Impact — how much revenue was given away
Discount Amount: SUM([Sales] * [Discount])
// Return Rate (requires Returns table join)
Return Rate: COUNTD([Returned Order ID]) / COUNTD([Order ID])
📐
The Profit Ratio Formula — A Common Mistake
Never write
AVG([Profit] / [Sales]) for Profit Ratio. This computes profit ratio per row then averages those ratios — it's mathematically incorrect because small orders (low Sales denominator) get disproportionate weight. The correct formula is always SUM([Profit]) / SUM([Sales]) — sum first, then divide. This is the standard accounting method and matches what any finance team expects to see.🔑 Key Concepts — Lesson 6
Row-Level Calc
A formula evaluated for every single row of data. Example: [Sales] * (1 - [Discount]) computes discounted price per order row.
Aggregate Calc
Uses aggregation functions (SUM, AVG, COUNTD). Evaluated after grouping. Used for KPIs like Profit Ratio and AOV.
DATEDIFF
Calculates the difference between two dates in a specified unit (day, month, year). Essential for lead time and tenure calculations.
CASE / WHEN
Pattern matching for a single field against specific values. Cleaner than chained IF/ELSEIF when mapping codes to display labels.
COUNTD
COUNT DISTINCT — counts only unique values. Use instead of COUNT when you want the number of unique customers, orders, or products.
Profit Ratio
SUM(Profit) / SUM(Sales). The correct aggregate formula for profit margin. Always aggregate both numerator and denominator first.
🧠 Knowledge Check
1. What is the correct formula to calculate Profit Ratio (profit margin) as a percentage in Tableau?
2. Which function would you use to calculate the number of days between Order Date and Ship Date?
3. You want to count unique customers (not total orders) in a calculated field. Which function do you use?
4. Which string function extracts the first 4 characters from a field named [Order ID]?
5. Which logical structure is best for mapping a field's specific values to display labels (e.g., "N" → "North", "S" → "South", etc.)?
🏆
✅ Lesson Summary
What You Learned
📋
Lesson 6 — Key Takeaways
✅ Calculated fields create new fields using formulas — row-level vs aggregate distinction is critical
✅ String functions: UPPER, LEN, CONTAINS, LEFT/RIGHT, SPLIT, REPLACE, TRIM
✅ Date functions: DATEPART, DATEDIFF, DATEADD, TODAY, DATETRUNC for time analysis
✅ Logic: IF/ELSEIF/ELSE, IIF (compact), CASE/WHEN (pattern matching)
✅ Aggregations: SUM, AVG, COUNTD (unique count), MIN/MAX, MEDIAN
✅ KPI formulas: Profit Ratio = SUM(Profit)/SUM(Sales) · AOV = SUM(Sales)/COUNTD(Orders)
✅ String functions: UPPER, LEN, CONTAINS, LEFT/RIGHT, SPLIT, REPLACE, TRIM
✅ Date functions: DATEPART, DATEDIFF, DATEADD, TODAY, DATETRUNC for time analysis
✅ Logic: IF/ELSEIF/ELSE, IIF (compact), CASE/WHEN (pattern matching)
✅ Aggregations: SUM, AVG, COUNTD (unique count), MIN/MAX, MEDIAN
✅ KPI formulas: Profit Ratio = SUM(Profit)/SUM(Sales) · AOV = SUM(Sales)/COUNTD(Orders)