Tableau Calculations Cheat Sheet — Calculated Fields, LOD, Table Calc | Dataplexa

Tableau Calculations

Calculated fields · LOD expressions · Table calculations · Window functions · IF / CASE · Date & String functions

Sheet 2 of 2 Tableau 2024 Intermediate Printable

Calculated Fields — Overview

Analysis → Create Calculated Field

Types of Calculations

// 3 types in Tableau:

// 1. Row-level (Basic)
// Runs on every row of data
// e.g. [Profit] / [Sales]

// 2. Aggregate
// Aggregates across rows
// e.g. SUM([Sales]) / SUM([Profit])

// 3. Table Calculations
// Computed on the viz results
// e.g. RUNNING_SUM(SUM([Sales]))

// 4. LOD Expressions
// Control the level of detail
// e.g. {FIXED [Region]: SUM([Sales])}

Basic Calculated Field

// Profit Ratio
SUM([Profit]) / SUM([Sales])

// Discount Flag
IF [Discount] > 0
THEN "Discounted"
ELSE "Full Price"
END

// Days to Ship
DATEDIFF('day', [Order Date],
         [Ship Date])

// Full Name
[First Name] + " " + [Last Name]

// Null check
ISNULL([Region])
ZN([Sales])  // NULL → 0

Aggregation Functions

SUM([Sales])
AVG([Discount])
MIN([Order Date])
MAX([Sales])
COUNT([Order ID])
COUNTD([Customer ID])  // distinct count
MEDIAN([Sales])
STDEV([Sales])
VAR([Profit])
ATTR([Category])    // single value or *

// % of total shortcut
SUM([Sales]) /
TOTAL(SUM([Sales]))

Row-level vs Aggregate: Row-level calcs use field names like [Sales]. Aggregate calcs wrap fields in functions like SUM([Sales]). You cannot mix row-level and aggregate in the same calc without using an LOD.

IF / CASE Logic

IF · ELSEIF · IIF · CASE · WHEN

IF / ELSEIF / ELSE

// Basic IF
IF [Profit] > 0
THEN "Profit"
ELSE "Loss"
END

// Multiple conditions
IF SUM([Sales]) >= 100000
THEN "High"
ELSEIF SUM([Sales]) >= 50000
THEN "Medium"
ELSE "Low"
END

// IIF — inline if (3 args)
IIF([Profit] > 0,
    "Profit",
    "Loss")

// Boolean calc
[Sales] > 1000 AND [Discount] = 0

CASE / WHEN

// CASE on a dimension
CASE [Region]
WHEN "East"  THEN "Eastern US"
WHEN "West"  THEN "Western US"
WHEN "South" THEN "Southern US"
ELSE "Other"
END

// CASE for numeric buckets
CASE TRUE
WHEN [Age] < 18  THEN "Minor"
WHEN [Age] < 65  THEN "Adult"
ELSE "Senior"
END

// AND / OR / NOT
[Category] = "Furniture"
  AND [Sub-Category] = "Chairs"

NOT ISNULL([Manager])

LOD Expressions — Level of Detail

FIXED · INCLUDE · EXCLUDE

FIXED — ignore view filters

// Syntax
{ FIXED [dim1], [dim2] : AGG([measure]) }

// Sales per Customer (fixed)
{ FIXED [Customer ID] :
  SUM([Sales]) }

// First order date per customer
{ FIXED [Customer ID] :
  MIN([Order Date]) }

// Max sales per region
{ FIXED [Region] :
  MAX(SUM([Sales])) }

// No dimension — grand total
{ FIXED : SUM([Sales]) }

// % of grand total
SUM([Sales]) /
{ FIXED : SUM([Sales]) }

INCLUDE & EXCLUDE

// INCLUDE — adds detail
// Computes at finer grain than view
{ INCLUDE [Customer ID] :
  SUM([Sales]) }

// Avg orders per customer by region
AVG(
  { INCLUDE [Customer ID] :
    COUNTD([Order ID]) }
)

// EXCLUDE — removes detail
// Computes at coarser grain than view
{ EXCLUDE [Sub-Category] :
  SUM([Sales]) }

// % of category total
SUM([Sales]) /
{ EXCLUDE [Sub-Category] :
  SUM([Sales]) }

LOD Use Cases

// New vs Returning customer
IF [Order Date] =
   { FIXED [Customer ID] :
     MIN([Order Date]) }
THEN "New"
ELSE "Returning"
END

// Customer tier by total spend
IF { FIXED [Customer ID] :
      SUM([Sales]) } >= 10000
THEN "Platinum"
ELSEIF { FIXED [Customer ID] :
          SUM([Sales]) } >= 5000
THEN "Gold"
ELSE "Standard"
END

FIXED vs filters: FIXED expressions ignore dimension filters unless you promote them to context filters. INCLUDE and EXCLUDE respect all filters. Use FIXED when you need a calculation that is completely independent of what the user filters.

Table Calculations

RUNNING · WINDOW · RANK · LOOKUP

Running & Window Functions

// Running total
RUNNING_SUM(SUM([Sales]))

// Running average
RUNNING_AVG(SUM([Sales]))

// Running min / max
RUNNING_MIN(SUM([Sales]))
RUNNING_MAX(SUM([Profit]))

// Window (all rows in partition)
WINDOW_SUM(SUM([Sales]))
WINDOW_AVG(SUM([Sales]))
WINDOW_MAX(SUM([Sales]))

// Window with offsets
// WINDOW_SUM(expr, start, end)
WINDOW_SUM(SUM([Sales]), -1, 0)
// current + previous row

RANK · INDEX · LOOKUP

// Rank (lower value = rank 1)
RANK(SUM([Sales]))
RANK(SUM([Sales]), 'asc')   // ascending
RANK(SUM([Sales]), 'desc')  // descending

// Unique rank (no ties)
RANK_UNIQUE(SUM([Sales]))
RANK_DENSE(SUM([Sales]))

// Row index (1, 2, 3...)
INDEX()

// Total rows in partition
SIZE()

// Value from relative row
LOOKUP(SUM([Sales]), -1)  // prev row
LOOKUP(SUM([Sales]),  1)  // next row
FIRST()   // offset from first row
LAST()    // offset from last row

Period-over-period growth: (SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / ABS(LOOKUP(SUM([Sales]), -1)) — gives % change vs previous period.

Date Functions

DATEPART · DATEDIFF · DATEADD · TODAY

Extract & Truncate

// DATEPART — extract component
DATEPART('year',    [Order Date])
DATEPART('month',   [Order Date])
DATEPART('quarter', [Order Date])
DATEPART('week',    [Order Date])
DATEPART('weekday', [Order Date])
DATEPART('day',     [Order Date])

// DATETRUNC — snap to period start
DATETRUNC('month',   [Order Date])
DATETRUNC('quarter', [Order Date])
DATETRUNC('year',    [Order Date])

DATEDIFF · DATEADD · TODAY

// DATEDIFF — gap between dates
DATEDIFF('day',
         [Order Date],
         [Ship Date])

DATEDIFF('month',
         [Start Date], TODAY())

// DATEADD — shift a date
DATEADD('day',   30, [Order Date])
DATEADD('month', -1, TODAY())

// NOW / TODAY
TODAY()             // current date
NOW()               // current datetime

// Days since order
DATEDIFF('day',
         [Order Date], TODAY())

// Is in last 30 days?
[Order Date] >=
DATEADD('day', -30, TODAY())

String Functions

CONTAINS · LEFT · MID · REPLACE · SPLIT · REGEXP

Search & Test

// Contains (case-sensitive)
CONTAINS([Product Name], "Chair")

// Starts / ends with
STARTSWITH([Name], "A")
ENDSWITH([Email], ".com")

// Find position (0 = not found)
FIND([Product Name], "Chair")

// Case-insensitive match
CONTAINS(
  LOWER([Product Name]),
  "chair"
)

// Length
LEN([Customer Name])

Extract & Transform

// LEFT / RIGHT / MID
LEFT([Order ID], 4)
RIGHT([Postal Code], 4)
MID([Order ID], 5, 4)

// REPLACE
REPLACE([Product Name],
        " ", "_")

// SPLIT — extract token by delim
SPLIT([Order ID], "-", 1)
// "CA-2023-12345" → "CA"

// Upper / Lower / TRIM
UPPER([Category])
LOWER([Email])
TRIM([Customer Name])
LTRIM([Name])
RTRIM([Name])

STR, INT, FLOAT Conversions & REGEXP

// Type conversions
STR([Quantity])
INT([Price])
FLOAT([Quantity])
DATE([Order Date String])

// Build display string
[Customer Name] + " (" +
STR(DATEPART('year',[Order Date]))
+ ")"

// REGEXP (Hyper / some connectors)
REGEXP_MATCH([Email],
  '.+@.+\..+')

REGEXP_EXTRACT([URL],
  'utm_source=([^&]+)')

REGEXP_REPLACE([Text],
  '\s+', ' ')

Number Functions

ROUND · ABS · POWER · ZN · PERCENTILE

Common Math

ROUND([Sales], 2)      // 2 decimal places
ROUND([Sales], -3)     // round to 1000s
CEILING([Price])       // round up
FLOOR([Price])         // round down
ABS([Profit])          // absolute value
POWER([Value], 2)      // square
SQRT([Value])          // square root
LN([Revenue])          // natural log
LOG([Value], 10)       // log base 10
MIN([A], [B])           // row-level min
MAX([A], [B])           // row-level max

Null handling & Percentile

// ZN — convert NULL to 0
ZN([Sales])

// ISNULL — test for null
ISNULL([Manager])

// IFNULL — return default if null
IFNULL([Region], "Unknown")

// NULLIF — make value null conditionally
NULLIF([Discount], 0)

// Percentile (aggregation)
PERCENTILE([Sales], 0.75)
// 75th percentile

// Median shortcut
MEDIAN([Sales])

Parameters in Calculations

dynamic · user-controlled

Using Parameters

// Create parameter first:
// Analysis → Create Parameter
// Name: "Metric Selector"
// Data type: String
// Allowable values: List
//   Sales, Profit, Quantity

// Reference in calc with [Param Name]
CASE [Metric Selector]
WHEN "Sales"    THEN SUM([Sales])
WHEN "Profit"   THEN SUM([Profit])
WHEN "Quantity" THEN SUM([Quantity])
END

// Dynamic threshold
SUM([Sales]) >= [Sales Threshold]

// Dynamic date range
[Order Date] >=
DATEADD('day',
  -[Days Back Param],
  TODAY())

Show Parameter Control by right-clicking the parameter in the Data pane → Show Parameter. This adds a UI control to your dashboard so users can change the value without editing the calc.

Function Quick Reference

all functions at a glance

Function	Category	Syntax	Returns	Example
SUM / AVG / COUNT	Aggregate	SUM([Sales])	Number	SUM([Profit]) / SUM([Sales])
COUNTD	Aggregate	COUNTD([Customer ID])	Integer	COUNTD([Order ID])
IF / ELSEIF / ELSE	Logical	IF cond THEN v1 ELSE v2 END	Any	IF [Profit] > 0 THEN "+" ELSE "-" END
IIF	Logical	IIF(cond, true, false)	Any	IIF([Discount]=0, "None", "Yes")
CASE / WHEN	Logical	CASE [dim] WHEN v THEN r END	Any	CASE [Region] WHEN "East" THEN "E"
ISNULL / ZN / IFNULL	Null handling	ISNULL([f]) / ZN([f]) / IFNULL([f], val)	Bool/Any	IFNULL([City], "Unknown")
FIXED	LOD	{ FIXED [dim] : AGG([m]) }	Number	{ FIXED [Cust ID] : SUM([Sales]) }
INCLUDE / EXCLUDE	LOD	{ INCLUDE [dim] : AGG([m]) }	Number	{ EXCLUDE [Sub-Cat] : SUM([Sales]) }
RUNNING_SUM	Table Calc	RUNNING_SUM(SUM([Sales]))	Number	Cumulative total
WINDOW_SUM / AVG	Table Calc	WINDOW_SUM(SUM([Sales]))	Number	Total across partition
RANK / RANK_DENSE	Table Calc	RANK(SUM([Sales]), 'asc')	Integer	Rank by sales
LOOKUP	Table Calc	LOOKUP(SUM([Sales]), -1)	Number	Prior period sales
DATEPART	Date	DATEPART('month', [Date])	Integer	Extract month number
DATEDIFF	Date	DATEDIFF('day', [Start], [End])	Integer	Days to ship
DATEADD	Date	DATEADD('month', -1, TODAY())	Date	Prior month start
CONTAINS / FIND	String	CONTAINS([Name], "Chair")	Bool/Int	Product filter
SPLIT	String	SPLIT([Order ID], "-", 1)	String	Extract region code
REGEXP_EXTRACT	String	REGEXP_EXTRACT([URL], 'pattern')	String	Pull UTM source

Calculations Mastery Checklist

sheet 2 complete

Calculated Fields	Key point
Know the 4 calculation types	Row / Agg / Table / LOD
Write IF / ELSEIF / CASE logic	always end with END
Use IIF for inline conditionals	IIF(cond, true, false)
Handle nulls safely	ZN() / IFNULL() / ISNULL()

LOD & Table Calcs	Key point
Use FIXED to ignore filters	{ FIXED [dim] : AGG }
INCLUDE adds finer grain	avg per customer in region
EXCLUDE removes a dimension	% of category total
Create running totals	RUNNING_SUM(SUM([Sales]))

Dates, Strings & Params	Key point
Extract date parts	DATEPART('month', [Date])
Calculate date gaps	DATEDIFF('day', [A], [B])
Split fields by delimiter	SPLIT([ID], "-", 1)
Wire parameters to calcs	CASE [Param] WHEN ... END

Tableau series complete! · You've covered Basics & Calculations. Next steps: explore Dashboard actions, Sets & Groups, Blending vs Joining, and Tableau Prep for data cleaning.

Back to Cheat Sheets