Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 35 ยท Calculations

Tableau Calculated Fields: IF, CASE, Logical and Aggregate Functions

Create Tableau calculated fields with IF, IIF, CASE, logical, string and aggregate functions. Learn row-level versus aggregate calculations and fix the mix-aggregate error.

Calculated fields turn the data you have into the data you need: a profit margin, a size band, a clean label, a flag for late orders. Tableau's calculation language looks like a mix of Excel and SQL, and a small set of functions covers most work. The most important idea is the level at which a calculation runs: row by row, or on aggregated values.

Tableau cannot run here, so the calculations below are read-only code, each paired with a runnable SQL or Python stand-in that gives the same result on a small table.

Creating a calculated field

  1. In the Data pane, click the small drop-down arrow > Create Calculated Field... (or use Analysis > Create Calculated Field...).
  2. Type a name (Profit Ratio) and the formula. The editor colors fields, checks syntax and shows "The calculation is valid" at the bottom.
  3. Click OK. The field appears in the Data pane: green if the result is numeric, blue if it is text or a date.
  4. Drag it to the view like any other field.

Fields are written in square brackets, strings in double quotes, comments after //.

TEXT
// Profit margin per region
SUM([Profit]) / SUM([Sales])

Row-level versus aggregate calculations

A row-level calculation runs on every data row, before aggregation: [Sales] - [Profit]. An aggregate calculation uses aggregate functions and runs on the grouped values in the view: SUM([Profit]) / SUM([Sales]).

The difference matters for ratios. A profit ratio per row averaged afterwards gives every order equal weight, whatever its size. A ratio of sums weights by money, and is almost always what the business means.

Python Stand-in: ratio of sums versus average of ratios

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first is 0.073, the second is -0.1 (negative!) because two small losing orders count as much as one large winner.

You cannot mix the two levels in one expression: [Sales] / SUM([Profit]) gives "Cannot mix aggregate and non-aggregate arguments with this function." Either aggregate everything (SUM([Sales]) / SUM([Profit])) or use a LOD expression to bring an aggregate down to row level. ATTR([Field]) returns the value when it is unique in the group, and * otherwise.

IF, ELSEIF and IIF

TEXT
IF [Profit] > 0 THEN "Profit"
ELSEIF [Profit] = 0 THEN "Break-even"
ELSE "Loss"
END
  • Every IF needs an END. Without an ELSE, unmatched rows return Null.
  • IIF(test, then, else, [unknown]) is a one-line version: IIF([Sales] > 500, "Large", "Small").
  • Combine conditions with AND, OR and NOT. Parenthesize when mixing them.

CASE

CASE compares one expression with several values. It is cleaner than a long IF chain when you test one field for equality.

TEXT
CASE [Segment]
  WHEN "Consumer"    THEN "B2C"
  WHEN "Corporate"   THEN "B2B"
  WHEN "Home Office" THEN "B2B"
  ELSE "Other"
END

Use IF for ranges (>, <) and mixed conditions; use CASE for fixed values.

SQL Stand-in: IF and CASE as SQL CASE

Press Run (or Ctrl + Enter) to see the result here.

Logical functions

Function Use
AND, OR, NOT Combine tests
IN [Region] IN ("East","West")
ISNULL(x), IFNULL(x, y), ZN(x) Detect or replace nulls
CONTAINS(s, sub), STARTSWITH, ENDSWITH Text tests
ISDATE(s) Is this text a date?
IIF(...) Inline if

Aggregate functions

These summarize many rows into one: SUM, AVG, COUNT, COUNTD (distinct count), MIN, MAX, MEDIAN, STDEV, VAR, PERCENTILE([x], 0.9), ATTR.

Two useful patterns:

TEXT
// Number of orders (distinct, so a join cannot inflate it)
COUNTD([Order ID])

// Conditional sum: only sales of Technology
SUM(IF [Category] = "Technology" THEN [Sales] END)

The second works because rows that fail the condition return Null, and SUM ignores nulls.

SQL Stand-in: conditional sum and distinct count

Press Run (or Ctrl + Enter) to see the result here.

Order 1 has two lines, so COUNT says 5 rows but COUNTD says 4 orders.

String and number functions

A short tour you will use often (text positions start at 1):

TEXT
UPPER([Name])   LOWER([Name])   TRIM([Name])   LEN([Name])
LEFT([Code], 3)   RIGHT([Code], 2)   MID([Code], 2, 3)
REPLACE([Phone], "-", "")   CONTAINS([Name], "Inc")
SPLIT([Full Name], " ", 1)   FIND([Email], "@")
ROUND([Value], 1)   CEILING(x)   FLOOR(x)   ABS(x)   POWER(x, 2)   SQRT(x)
STR(x)  INT(x)  FLOAT(x)  DATE(x)  DATETIME(x)     // type conversion

Remember division by zero: SUM([Profit]) / SUM([Sales]) is Null when the sales sum is zero, so wrap it when needed: IF SUM([Sales]) = 0 THEN 0 ELSE SUM([Profit]) / SUM([Sales]) END. In Tableau, integer division returns a decimal when needed (1/2 = 0.5); use DIV(a, b) for integer division.

Dates in calculations

You met DATETRUNC, DATEDIFF and DATEADD in Data types. Typical calculations:

TEXT
IF [Ship Date] > DATEADD('day', 7, [Order Date]) THEN "Late" ELSE "On time" END
DATEDIFF('day', [Order Date], TODAY())      // age in days

Naming and organizing

  • Give fields business names (Profit Margin, not Calc 1). Add a description via right-click > Default Properties > Comment.
  • Use folders (Data pane > Group by > Folder) to keep calculations tidy.
  • Keep one calculation per idea and build on earlier ones instead of copying long expressions.
  • Calculations are saved in the workbook; to reuse them, publish the data source.

Common mistakes

  • Mixing aggregate and non-aggregate arguments.
  • Averaging ratios when you need a ratio of sums.
  • Forgetting END on IF and CASE.
  • Comparing strings with different case or spaces ("east" does not equal "East ").
  • Null in a comparison. Null > 5 is Null, not false; handle nulls first.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which expression is valid in a Tableau calculation?

    Both sides are aggregated. The first mixes row-level and aggregate; the last is missing ELSE/END (and the third has a bracket missing).
  2. 2. What does COUNTD([Order ID]) return?

    COUNTD counts distinct values, so repeated order lines are counted once.
  3. 3. What does an IF without ELSE return when no branch matches?

    Unmatched rows are Null, which aggregate functions such as SUM ignore. This is the basis of conditional aggregation.
  4. 4. When is CASE a better fit than IF?

    CASE expresses equality tests against a single expression compactly.

Summary

  • Create calculated fields with the Data pane menu; names in [brackets], comments with //.
  • Know your level: row-level or aggregate. Do not mix them; use ratio of sums, not average of ratios.
  • IF/ELSEIF/ELSE/END and CASE create categories; IIF is the one-liner.
  • COUNTD and conditional SUM patterns solve many counting problems.
  • Handle nulls and zero division on purpose.

Next: Table calculations