All lessons

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:

Try it: write the DAX

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.

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):

Try it: write the DAX

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.

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:

Try it: write the DAX

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.

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

  1. Column or measure: (a) Sales last year, (b) “Big order” flag when Amount > 100, (c) Average price per unit?
  2. Why can’t you put a measure on a slicer?
  3. Where else could you create a “Weekend” column instead of DAX?
Answers
  1. (a) Measure. (b) Column (you’d filter by it). (c) Measure (total amount ÷ total quantity).
  2. A slicer lists stored values to filter by; a measure has no stored values, only a result per cell.
  3. In Power Query (Add column > Conditional column) or in the date table itself.

Check yourself

1When is a calculated column worked out?
2You want to put "Price band" (Budget, Mid, Premium) on a slicer. Column or measure?
3Why is Margin % better as a measure than as a column summed up?

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.