Module 3: Data modelling · Lesson 3.4 · about 16 min
Calculated columns or measures?
A calculated column is worked out once per row and stored; a measure is worked out on demand for each cell of a visual. Try both on the same margin calculation and learn when each is right.
Module 3 · about 16 minutes · read first: 3.1. You’ll be able to choose between a calculated column and a measure, and write both.
1. Why this matters
Beginners make everything a calculated column because it feels like Excel. Models then grow huge and ratios come out wrong. Knowing the difference is one of the first things a Power BI interview checks.
2. The difference in one table
| Calculated column | Measure | |
|---|---|---|
| Worked out | at refresh, once per row | when a visual asks, once per cell |
| Stored? | yes, takes memory | no |
| Has a current row? | yes (row context) | no (filter context) |
| Can go on a slicer, axis or rows | yes | no |
| Good for | attributes to group or filter by | numbers that must respect filters: totals, ratios, time comparisons |
3. Try a column
A calculated column has a current row, so it can use the row’s columns directly:
Add a calculated column Sales[Margin] that is the Amount minus Quantity × UnitCost for each row. The measure below adds it up; run to check.
Press Run to see the result.
In a calculated column there is a current row, so you can use the columns of Sales directly: Sales[Amount] - Sales[Quantity] * Sales[UnitCost].
Sales[Margin] = Sales[Amount] - Sales[Quantity] * Sales[UnitCost]
Total Margin = SUM(Sales[Margin]) 4. The same thing as a measure
Instead of storing a margin for 7,328 rows, a measure can work it out on demand with an iterator (SUMX, Lesson 4.3):
Now get the same total margin with a measure only (no new column), using SUMX to work row by row. You will learn SUMX properly in Lesson 4.3.
Press Run to see the result.
SUMX(Sales, expression) works the expression out for each row of Sales and adds them up. The expression is the same as the column formula.
Total Margin = SUMX(Sales, Sales[Amount] - Sales[Quantity] * Sales[UnitCost]) Both give the same totals. The measure takes no memory and works in any visual. In a model with 50 million rows, that difference matters a lot.
5. Where columns are right
Use a column when you need to group or filter by the result: a price band, an age group, “Weekend / Weekday”, a product’s category copied into Sales (rarely needed, since relationships already do that). And prefer to create such columns in Power Query or the source, which compresses better and keeps DAX for calculations. A good rule from the Power BI community: “as far upstream as possible, as far downstream as necessary”.
Columns can follow relationships with RELATED:
Add a calculated column Sales[Category] that copies each line's product category from the Product table, using RELATED. The query then counts lines per category.
Press Run to see the result.
RELATED follows the relationship from the current Sales row to its Product row: RELATED(Product[Category]).
Sales[Category] = RELATED(Product[Category])
EVALUATE SUMMARIZECOLUMNS(Sales[Category], "Lines", COUNTROWS(Sales)) Principle
Principle: columns describe rows; measures summarise sets of rows. If the answer depends on which rows are selected (a total, an average, a ratio, “vs last year”), it’s a measure. If it’s a fact about one row that never depends on the report, it can be a column.
Ratios: for Margin % the correct value for a group is ΣMargin / ΣSales, not the sum or average of row percentages. Only a measure computes the sums in the current filter context first.
6. Worked example
Margin % by category. A column Sales[Margin %] = DIVIDE(Sales[Amount] - Sales[Quantity] * Sales[UnitCost], Sales[Amount]) summed in a visual gives values like 3,412 %: nonsense, because it adds percentages. Averaging them weights a $15 sale the same as a $500 one. The measure Margin % = DIVIDE([Total Margin], [Total Sales]) divides the totals for each category: Electronics about 49 %, Grocery about 42 %, Home and Outdoor about 54 %.
7. Practice
- Column or measure: (a) Sales last year, (b) “Big order” flag when Amount > 100, (c) Average price per unit?
- Why can’t you put a measure on a slicer?
- Where else could you create a “Weekend” column instead of DAX?
Answers
- (a) Measure. (b) Column (you’d filter by it). (c) Measure (total amount ÷ total quantity).
- A slicer lists stored values to filter by; a measure has no stored values, only a result per cell.
- In Power Query (Add column > Conditional column) or in the date table itself.
Check yourself
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.