2.3Calculated columns vs measures
The essentials in 30 seconds
- The measure calculates an aggregate on the fly according to the visual's context (nothing is stored): it is the heart of Power BI.
- The calculated column adds a value to each row, calculated at refresh and stored: it makes the model bigger.
- Reflex: measure first; the calculated column is reserved for row-by-row needs (filter, categorise, link) — what must react to filters is a measure.
Running mini-case — Zineb decides storage or on-the-fly
Zineb, a management controller in an agri-food SME in Algiers, wants a margin per sales line and a total that recalculates by city. She thinks she has to put everything in a calculated column. She applies the section: the margin per line = Sales[Amount] * 0.3 is a calculated column (stored value, row by row), whereas Total Revenue = SUM(Sales[Amount]) is a measure (on the fly). She sets up a matrix City × [Total Revenue]: the measure recalculates by city, by total, whereas a column would stay frozen. She remembers the decisive test: what must react to slicers is a measure. Reflex installed: measure first, column if you need row by row.
Standalone case for this section: it returns in the course, the diagram, the script and the 48h challenge of section 2.3.
1. Two ways to calculate
The calculated column adds a value to EACH ROW of the table, calculated at refresh and stored in memory — for example Margin = Sales[Amount] * 0.3. The measure calculates an aggregate ON THE FLY according to the visual's context and stores nothing — for example Total Revenue = SUM(Sales[Amount]). Zineb quickly understands which of the two she is looking at.
2. The decision tree
A calculated column lives in a row context ("the current row"); a measure lives in a filter context ("what the visual filters" — the city of the bar, the month of the column). The decisive test: what must react to the report's filters is a measure. A column that you only ever sum should have been a measure. Zineb asks herself the question for every calculation.
3. Organising your measures
Good habit from now on: group the measures in a dedicated "_Measures" table for a clean model. We keep calculated columns rare, because they make the file bigger. Zineb organises her measures and limits columns to the strict row-by-row needs.
🔎 Concrete example — 🔎 Concrete example — The column vs measure duel
Zineb creates both and compares:
Calculated column: Margin = Sales[Amount] * 0.3 (one value per row)
Measure : Total Revenue = SUM(Sales[Amount]) (lives in the visuals)
→ matrix City × [Total Revenue]: the measure recalculates by city, by total.Reading: the measure is recalculated in each cell of the visual (by city, by total).
Lesson: the column shows a frozen value; the measure adapts to the context.
Section vocabulary
- Calculated column
- New DAX column evaluated row by row and STORED: Margin = Sales[Amount] - Sales[Cost].
Simple example: Zineb calculates a margin per sales line.
What it is not: an aggregate that recalculates according to the visual.
Reflex: row-by-row need (filter, link) → column.
- Measure
- DAX calculation evaluated on demand in the visual's context: Total Revenue = SUM(Sales[Amount]).
Simple example: Zineb puts [Total Revenue] in a matrix City × Month.
What it is not: a stored value frozen at refresh.
Reflex: what must react to filters → measure.
- Row context
- The environment of a calculated column: "the current row".
Simple example: Zineb reads the margin of each sale, row by row.
What it is not: the dynamic filtering of a visual.
Reflex: calculated column → think "current row".
- Filter context
- The environment of a measure: "what the visual filters" (the city of the bar, the month of the column).
Simple example: Zineb recalculates [Total Revenue] by city automatically.
What it is not: a frozen value, identical everywhere.
Reflex: measure → think "what the visual filters".
- Implicit measure
- The automatic aggregation of a column dragged into values: handy at first, to be replaced by real explicit measures.
Simple example: Zineb replaces her automatic aggregations with [Total Revenue].
What it is not: an explicit measure, named and formatted.
Reflex: as soon as you go beyond the sum, create an explicit measure.
In practice — In practice — The column vs measure duel
- Calculated column: Table view, Sales table → New column → Margin = Sales[Amount] * 0.3 — observe the value on each row, like Zineb.
- Measure: New measure → Total Revenue = SUM(Sales[Amount]) — nothing shows in the table: it lives in the visuals.
- Create a matrix City × [Total Revenue]: the measure recalculates by city, by total — the context in action.
- Create the measure Average basket = AVERAGE(Sales[Amount]) and compare it with the average of the Margin column: the storage vs on-the-fly difference.
Step-by-step script — decide like Zineb (6 min)
1) Create a calculated column: Margin = Sales[Amount] * 0.3, and watch the value appear on each row. 2) Create a measure: Total Revenue = SUM(Sales[Amount]) — it does not show in the table. 3) Put [Total Revenue] in a matrix City × [Total Revenue]: it recalculates by city. 4) Add Average basket = AVERAGE(Sales[Amount]) and compare. 5) Store your measures in a dedicated "_Measures" table, like Zineb.
If a calculation must react to slicers, do not take a column: create a measure.
Copy-paste template — measure or column?
What must REACT to filters (slicers, clicks) → MEASURE (on the fly)
Need for a value ROW BY ROW (filter, categorise, link) → COLUMN
Column: Margin = Sales[Amount] * 0.3
Measure: Total Revenue = SUM(Sales[Amount])
Rule: measure first; store the measures in a "_Measures" table.Margin per row
Zineb calculates Margin = Sales[Amount] * 0.3 in a column: the value exists on each row (useful for filtering).
Total that reacts
Zineb puts [Total Revenue] as a measure: the total recalculates by city, by month, according to the slicers.
Average basket
Average basket = AVERAGE(Sales[Amount]): a measure that adapts to the visual's context.
Table _Measures
Zineb groups all her measures in a dedicated "_Measures" table: the model stays clean.
✓ Best practices
- Measure first: it reacts to filters and stores nothing.
- Calculated column only for a row-by-row need (filter, link).
- Store the measures in a dedicated "_Measures" table.
✗ Common pitfalls
- Creating a calculated column that you only ever sum.
- Multiplying calculated columns (they make the model bigger).
- Expecting a column to react to slicers (it is frozen).
Key takeaways
- Measure = calculation on the fly according to the visual's context: the heart of Power BI.
- Calculated column = value stored row by row: for filtering, categorising, linking — not for aggregating.
- Reflex: measure first. A column you only ever sum should have been a measure.
- Organise your measures: a dedicated "_Measures" table groups them cleanly.
48h challenge — apply it tomorrow
Replay Zineb's duel: create a calculated column (Margin = Sales[Amount] * 0.3) and a measure (Total Revenue = SUM(Sales[Amount])), put the measure in a matrix and verify that it recalculates by city. Write in one sentence why what reacts to filters is a measure.
- I distinguish a calculated column from a measure.
- I choose measure first and column if I need row by row.
- I organise my measures and explain the context (row vs filter).
Frequently asked questions
Why does my calculated column not react to slicers?
By nature: a calculated column is evaluated at refresh, row by row, and its value is stored once and for all. It is therefore frozen and does not see the report's filters. What must react to slicers must be a measure, because a measure is recalculated on the fly in the visual's context.
Are implicit measures (dragging a column) enough?
To start, yes: dragging a numeric column creates an automatic aggregation. But explicit measures quickly become essential: reusable, formattable, documentable, and mandatory for any non-trivial calculation (ratios, time intelligence) and for the PL-300. Store them in a "_Measures" table.
What must react to the report's filters (slicers, clicks)?