Almost every DAX beginner asks the same question: "Should I create a calculated column or a measure?" Pick wrong and your report can show incorrect totals, refresh slowly, or carry a model that's much bigger than it needs to be.
The good news is that there's a simple rule, and once you see why it works, you'll rarely have to think about it again.
What you'll learn
- What calculated columns and measures are, and when each one is calculated
- Why summing or averaging a calculated ratio gives the wrong answer
- A simple decision rule you can apply in seconds
- Why some columns are better created in Power Query than in DAX
The problem
Coming from Excel, the natural instinct is to add a new column for every calculation: a column for line total, a column for profit, a column for margin %. It feels familiar, and it works for simple things. But in Power BI the same habit quietly causes two problems:
- Wrong numbers. Row-level values can't be correctly rolled up into totals for ratios and percentages.
- A bloated model. Every calculated column is stored in memory for every row, which slows refreshes and makes files larger.
The solution
What is a calculated column?
A calculated column is a new column added to a table. DAX evaluates the formula once for each row when the data is loaded or refreshed, and stores the result in the model, just like any other column.
Line Total = Sales[Quantity] * Sales[UnitPrice]
Price Band =
SWITCH (
TRUE (),
Sales[UnitPrice] < 50, "Low",
Sales[UnitPrice] < 500, "Medium",
"High"
)
Full Name = Customer[FirstName] & " " & Customer[LastName]Because these are real columns, you can use them as slicers, as axes on a chart, in filters, and in relationships.
What is a measure?
A measure is a formula that is calculated on the fly, only when a visual asks for it, using whatever filters are currently applied. Nothing is stored except the formula itself.
Total Sales = SUM ( Sales[SalesAmount] )
Total Profit = SUM ( Sales[Profit] )
Profit Margin % =
DIVIDE ( [Total Profit], [Total Sales] )Why the difference matters: a worked example
Imagine two orders, and a calculated column that stores the margin % for each row:
| Order | Sales | Profit | Margin % (calculated column) |
|---|---|---|---|
| A | 100 | 50 | 50% |
| B | 900 | 90 | 10% |
| Total | 1,000 | 140 | ? |
If you average the margin column you get 30%. But the business actually earned 140 on sales of 1,000, which is 14%. The column treats a 100-unit order the same as a 900-unit order, which is simply wrong at the total level.
The measure version gets it right, because it adds up the numerator and denominator first and divides at the end:
Profit Margin % =
DIVIDE (
SUM ( Sales[Profit] ),
SUM ( Sales[SalesAmount] )
)This returns 14% at the grand total, and the correct percentage for any slicer selection, category, or month.
How it works
| Calculated column | Measure | |
|---|---|---|
| When calculated | At data refresh, row by row | At query time, when a visual needs it |
| Stored in model | Yes, uses memory and file size | No, only the formula |
| Context | Row context | Filter context (slicers, filters, visual layout) |
| Use in slicers, axes, filters | Yes | No (they are values, not attributes) |
| Use in relationships | Yes | No |
| Reacts to slicer selections | No, the value is fixed | Yes |
| Best for | Categories, labels, buckets, keys | Totals, ratios, percentages, KPIs |
The decision rule
- Will you use it to slice, filter, group or relate tables? Make it a calculated column (or better, see below).
- Will you aggregate it or show it as a number in a visual? Make it a measure.
If it's a number the user will see and it might change when they click a slicer, it's a measure. If it's a label that describes each row, it's a column.
Measures can still use row-level logic
You don't need a calculated column just because the logic works row by row. An iterator such as SUMX does the row-by-row work inside a measure, with nothing stored in the model:
Total Sales =
SUMX (
Sales,
Sales[Quantity] * Sales[UnitPrice]
)This gives you the same result as summing a Line Total column, without storing a column of millions of values.
Often the best place for a column is not DAX
If you do need a new column, ask whether it can be created upstream: in Power Query (Add Column) or in your source database or SQL view. Columns created there compress better, load faster, and keep your DAX model lean. Reserve DAX calculated columns for logic that genuinely needs the model, such as using values from related tables.
Common mistakes
- Creating a calculated column for every calculation. The most common habit carried over from Excel, and the main reason beginner models are large and slow.
- Calculating percentages and ratios in a column. As the example showed, summing or averaging a row-level ratio gives the wrong total. Always divide the sums, in a measure.
- Trying to use a measure in a slicer or on an axis. Measures return values, not attributes. If you need to slice by it, such as a price band, you need a column.
- Forgetting that columns don't react to slicers. A calculated column is evaluated at refresh, so it can't know what the user selected afterwards.
- Leaving unnecessary columns in the model. After you've built measures, delete or hide helper columns that nothing uses.
Key takeaways
- Calculated columns are computed at refresh and stored; measures are computed at query time and respond to filters.
- Use columns for attributes you slice, filter or relate by; use measures for anything you aggregate or display as a number.
- Never roll up a row-level ratio. Build the ratio as a measure.
- When in doubt, start with a measure, and create columns upstream in Power Query when you can.
Related posts
- What is DAX? A beginner's guide
- Row context vs. filter context (coming soon)
- CALCULATE explained with examples (coming soon)
Not sure whether something should be a column or a measure? Describe it in the comments below and I'll help you decide.