10 Creating Custom Calculations and Fields

What This Chapter Covers

Tableau’s built-in fields and measures only take you so far. Custom calculated fields are where you go beyond the data that was given to you and start deriving the metrics and dimensions that answer your specific business questions. In this chapter, you will learn the four types of Tableau calculations, basic calculations, string and date functions, aggregate calculations, and table calculations, and how to write each type using Tableau’s formula syntax. You will also learn about Level of Detail (LOD) expressions, which are among the most powerful and distinctive features of Tableau. By the end of this chapter, you will be able to derive virtually any metric from raw data using Tableau’s calculation engine.

flowchart TD
    A[Tableau Calculations] --> B[Basic Row-Level <br> Calculations]
    A --> C[Aggregate <br> Calculations]
    A --> D[Table <br> Calculations]
    A --> E[LOD <br> Expressions]
    B --> B1[IF/CASE logic <br> String/Date functions <br> Arithmetic]
    C --> C1[SUM, AVG, MIN <br> MAX, COUNT, COUNTD]
    D --> D1[Running Total <br> Percent of Total <br> Rank, Window functions]
    E --> E1[FIXED <br> INCLUDE <br> EXCLUDE]
    style A fill:#e3f2fd,stroke:#1976D2
    style B fill:#f3e5f5,stroke:#7B1FA2
    style C fill:#fff9c4,stroke:#F9A825
    style D fill:#fce4ec,stroke:#C62828
    style E fill:#e8f5e9,stroke:#388E3C

10.1 The Calculation Editor

Opening and Using the Calculation Editor

To create a calculated field in Tableau:

Right-click any blank area in the Data pane and select Create Calculated Field, or go to Analysis > Create Calculated Field.
The Calculation Editor opens. It has three components:
- Name field (top left), Enter a descriptive name for the new field.
- Formula area (main pane), Write your calculation here.
- Field reference panel (right), Browse all available functions, organised by category (Number, String, Date, Aggregate, Table Calculation, LOD, Logical, Type Conversion).
As you type, Tableau provides autocomplete suggestions for function names and field names.
A green checkmark at the bottom confirms the formula is syntactically valid. A red X indicates an error, hover over it to read the error message.
Click OK to save. The new calculated field appears in the Data pane, as a dimension (blue) or measure (green) depending on whether it returns a string/boolean or a number.

[Insert screenshot of the Tableau Calculation Editor with an IF/THEN formula entered, the green checkmark visible, and the function browser on the right]

10.2 Type 1: Basic Row-Level Calculations

Row-Level Calculations: Applied to Every Row

A basic (row-level) calculation is evaluated for every row in your dataset before any aggregation takes place. It is analogous to adding a new column to a spreadsheet.

Arithmetic calculations:

Code

# Profit margin ratio (row-level)
[Profit] / [Sales]

Code

# Revenue after applying a custom tax rate
[Sales] * (1 - [Tax Rate])

IF/THEN conditional logic:

Code

# Classify orders by size
IF [Sales] >= 5000 THEN "Large Order"
ELSEIF [Sales] >= 1000 THEN "Medium Order"
ELSE "Small Order"
END

CASE/WHEN for multiple conditions:

Code

# Map abbreviated region codes to full names
CASE [Region]
  WHEN "E" THEN "East"
  WHEN "W" THEN "West"
  WHEN "S" THEN "South"
  WHEN "N" THEN "North"
  ELSE "Unknown"
END

String Functions: Working with Text Fields

Function	Syntax	Example Use
`CONTAINS`	`CONTAINS([Field], "text")`	Filter records containing a keyword
`LEFT`	`LEFT([Field], n)`	Extract first n characters
`RIGHT`	`RIGHT([Field], n)`	Extract last n characters
`MID`	`MID([Field], start, length)`	Extract a substring
`TRIM`	`TRIM([Field])`	Remove leading/trailing spaces
`UPPER` / `LOWER`	`UPPER([Field])`	Convert case
`REPLACE`	`REPLACE([Field], "old", "new")`	Substitute text
`LEN`	`LEN([Field])`	Count characters

Code

# Extract the first word of a product name
LEFT([Product Name], FIND([Product Name], " ") - 1)

Date Functions: Working with Date Fields

Function	Syntax	Description
`DATEPART`	`DATEPART('month', [Date])`	Extract a date component as a number
`DATENAME`	`DATENAME('month', [Date])`	Extract a date component as a string
`DATEDIFF`	`DATEDIFF('day', [Start], [End])`	Calculate days between two dates
`DATEADD`	`DATEADD('month', 3, [Date])`	Add a time period to a date
`TODAY`	`TODAY()`	Return today’s date
`NOW`	`NOW()`	Return current date and time

Code

# Days from order to shipment (fulfilment time)
DATEDIFF('day', [Order Date], [Ship Date])

Code

# Flag orders placed in the current year
IF YEAR([Order Date]) = YEAR(TODAY()) THEN "Current Year"
ELSE "Prior Year"
END

10.3 Type 2: Aggregate Calculations

Aggregate Calculations: Applied After Grouping

Aggregate calculations apply statistical functions to groups of rows, producing a single value per dimension member. They are equivalent to SQL GROUP BY functions.

Core aggregate functions:

Function	Syntax	Description
`SUM`	`SUM([Sales])`	Sum of all values in the group
`AVG`	`AVG([Profit])`	Arithmetic mean
`MIN` / `MAX`	`MIN([Order Date])`	Minimum or maximum value
`COUNT`	`COUNT([Order ID])`	Count of non-null values
`COUNTD`	`COUNTD([Customer ID])`	Count of distinct values
`MEDIAN`	`MEDIAN([Sales])`	Median value (50th percentile)
`STDEV`	`STDEV([Sales])`	Sample standard deviation
`VAR`	`VAR([Profit])`	Sample variance
`PERCENTILE`	`PERCENTILE([Sales], 0.9)`	90th percentile

Code

# Profit margin as an aggregate ratio
SUM([Profit]) / SUM([Sales])

Note: AVG([Profit]) / AVG([Sales]) gives a different (and usually incorrect) result. Always aggregate both the numerator and denominator separately before dividing to get a true ratio.

The Most Common Aggregate Calculation Error

A very frequent mistake is computing a ratio by averaging the ratio at the row level:

Code

# WRONG: Average of row-level ratios
AVG([Profit] / [Sales])

This weights every order equally regardless of its size. A $10 order and a $10,000 order each count as one data point. The correct approach weights by revenue:

Code

# CORRECT: Ratio of aggregates (revenue-weighted margin)
SUM([Profit]) / SUM([Sales])

Always think about whether you want a simple average or a weighted ratio before writing your formula.

10.4 Type 3: Table Calculations

Table Calculations: Computed Across the View

Table calculations are applied to the aggregated values that are already in your chart, after the query has returned results from the database. They allow you to compute metrics that depend on the position or ordering of marks within the view: running totals, percentages of total, rankings, and window statistics.

Key table calculation types:

Calculation	Purpose	Example
Running Total	Cumulative sum across the view	Year-to-date cumulative revenue
Percent of Total	Each value as % of the grand total	Each region’s share of company sales
Rank	Ordinal rank of each mark	Which sub-category is ranked 3rd by profit?
Difference	Value change from previous mark	Month-over-month sales change
Percent Difference	% change from previous mark	Monthly revenue growth rate
Moving Average	Smoothed average over a window	3-month rolling average of orders
Window Sum/Avg/Max	Aggregate over a defined window	Total sales in the same quarter across all years

How To: Creating a Year-to-Date Cumulative Revenue Chart

Build a line chart with Order Date (Month, continuous) on Columns and Sales on Rows.
Right-click SUM(Sales) on the Rows shelf and select Add Table Calculation.
In the dialog, select Running Total as the Calculation Type and Sum as the aggregation.
Set Compute Using to Table (across) to calculate the running total left-to-right across months.
Click OK. The line chart now shows the cumulative year-to-date sales.
To compare multiple years: drag YEAR(Order Date) to the Colour shelf, you now have a YTD line per year.

[Insert screenshot of a year-to-date cumulative sales line chart with three years compared, each a different colour]

How To: Computing Percent of Total with Table Calculations

Build a bar chart with Sub-Category on Rows and Sales on Columns.
Right-click SUM(Sales) on Columns and select Add Table Calculation > Percent of Total.
Set Compute Using to Table (across).
Each bar now shows that sub-category’s percentage contribution to total sales.
Right-click the axis and format it as a Percentage (0 decimal places).

Code

# Equivalent manual table calculation
SUM([Sales]) / TOTAL(SUM([Sales]))

Understanding “Compute Using”, The Most Confusing Aspect of Table Calculations

The “Compute Using” setting determines the direction and partition of a table calculation, which dimension it runs across and which dimension it restarts for. Getting this wrong produces completely incorrect results that still look plausible.

The key rules: - Table (across): Computes left to right across the columns of the view. - Table (down): Computes top to bottom down the rows. - Pane (across/down): Resets the calculation for each pane in the view. - Cell: Computes separately for each mark. - Specific dimensions: The most precise option, you choose exactly which dimension the calculation addresses and which it partitions by.

When in doubt, select Specific Dimensions and manually specify which dimensions the calculation should run along. This gives you complete control and avoids surprises when you add or remove dimensions from the view.

10.5 Type 4: Level of Detail (LOD) Expressions

What Are LOD Expressions?

Level of Detail (LOD) expressions allow you to compute aggregations at a granularity that is different from the current view. This is one of Tableau’s most powerful and distinctive features, it solves a class of problems that were previously impossible without pre-aggregating data outside Tableau.

There are three LOD expression types:

FIXED: Computes the aggregation at the specified dimension, regardless of what dimensions are in the view.

Code

# Average order value per customer (fixed at customer level)
{ FIXED [Customer Name] : AVG([Sales]) }

INCLUDE: Computes the aggregation including the specified dimension in addition to the dimensions in the view.

Code

# Average order value per order within each sub-category view
{ INCLUDE [Order ID] : SUM([Sales]) }

EXCLUDE: Computes the aggregation excluding the specified dimension from the view.

Code

# Category-level total sales, regardless of sub-category in view
{ EXCLUDE [Sub-Category] : SUM([Sales]) }

Practical LOD Examples

1. Cohort analysis, first order date per customer:

Code

# First purchase date for each customer
{ FIXED [Customer Name] : MIN([Order Date]) }

Use this to identify which customers are new vs. returning in any given period.

2. Customer’s total lifetime spend:

Code

# Total spend across all orders per customer
{ FIXED [Customer ID] : SUM([Sales]) }

Drag this field to the Colour shelf in a scatter plot to see high-value customers highlighted.

3. Percent of category total (comparable to EXCLUDE):

Code

# Sub-category sales as % of its parent category total
SUM([Sales]) / { EXCLUDE [Sub-Category] : SUM([Sales]) }

4. Highest-order value per customer:

Code

# Single largest order value for each customer
{ FIXED [Customer Name] : MAX([Sales]) }

LOD Expressions vs. Table Calculations: When to Use Each

	Table Calculations	LOD Expressions
When computed	After aggregation, on the view	During query, before aggregation
Depends on view	Yes, adding/removing dimensions changes the result	FIXED does not; INCLUDE/EXCLUDE partially do
Can be filtered	Yes, by dimension filters	FIXED expressions are not affected by dimension filters (use Context Filters to override)
Best for	Running totals, % of total, rank, moving averages	Custom granularity, cohort analysis, cross-granularity ratios

Use table calculations when the calculation must reference the structure of the current view (e.g., cumulative total of what is visible). Use LOD expressions when the calculation needs to be anchored at a specific data granularity regardless of what the view shows.

10.6 Summary

Key Concepts at a Glance

Calculation Type	Evaluated When	Key Functions	Best For
Row-level	Per database row, before aggregation	IF, CASE, string/date functions	Derived dimensions, flags, text parsing
Aggregate	After GROUP BY, per dimension level	SUM, AVG, COUNTD, MEDIAN	Ratios, weighted averages, summary metrics
Table calculation	On aggregated values in the view	Running Total, % of Total, Rank, Window Avg	View-relative computations, YTD, rank
LOD expression	During query, at specified granularity	FIXED, INCLUDE, EXCLUDE	Cohort analysis, cross-granularity ratios

Applying This in Practice

When you encounter a metric that Tableau cannot build with a simple drag-and-drop, work through the four calculation types in order: Can a row-level calculation solve it? If not, an aggregate? If not, a table calculation? If not, an LOD expression? In most cases, the answer is at one of these four levels. LOD expressions are the solution of last resort, they are powerful but can be slow on large datasets and are more difficult to debug. Master the first three types thoroughly before reaching for LOD.