Measure vs Calculated Column in Power BI: When to Use Each
· 8 min read
The short answer to measure vs calculated column: a calculated column is computed once per row when the data refreshes and is stored in the table. A measure is computed on the fly for whatever the visual is showing (a category, a month, a slicer selection). Use a column when you need a value per row that you will filter, group or slice by. Use a measure for anything you add up, average or divide.
-- Calculated column on the Sales table: one value per row, stored
Line Revenue = Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct])
-- Measure: evaluated for each cell of the visual
Revenue = SUM ( Sales[Line Revenue] )The rest of this guide shows both on a small Brightline Supply Co. model, then the mistake that makes a margin report wrong without any error message.
The model used in the examples
Brightline sells office, kitchen and cleaning supplies to businesses. The examples use a small star schema:
- Sales (fact): one row per order line, with OrderID, ProductID, Quantity, UnitPrice, DiscountPct, Status.
- Products (dimension): ProductID, ProductName, Category, UnitCost.
- Customers and Date dimensions, linked to Sales by CustomerID and OrderDate.
Here are six completed order lines. Unit Cost comes from the Products table.
| OrderID | Product | Category | Quantity | UnitPrice | DiscountPct | Unit Cost |
|---|---|---|---|---|---|---|
| 50001 | Espresso Beans 1kg | Coffee & Kitchen | 4 | 24.00 | 0.00 | 11.50 |
| 50002 | Copy Paper A4 (500) | Paper & Print | 10 | 5.50 | 0.10 | 3.10 |
| 50003 | Paper Towels (24) | Cleaning | 3 | 25.00 | 0.00 | 12.40 |
| 50004 | Toner Cartridge Black | Paper & Print | 2 | 59.00 | 0.05 | 28.00 |
| 50006 | Tea Assortment (200) | Coffee & Kitchen | 5 | 19.90 | 0.00 | 9.40 |
| 50007 | Paper Towels (24) | Cleaning | 8 | 25.00 | 0.10 | 12.40 |
Calculated columns: one value per row
A calculated column is evaluated in row context: DAX looks at one row at a time, so Sales[Quantity] means "the quantity on this row". Add these three columns to Sales:
Line Revenue = Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct])
Line Cost = Sales[Quantity] * RELATED ( Products[UnitCost] )
Margin % = DIVIDE ( Sales[Line Revenue] - Sales[Line Cost], Sales[Line Revenue] )RELATED fetches the unit cost from Products through the relationship. It is covered in detail in RELATED, RELATEDTABLE and USERELATIONSHIP.
The new columns look like this:
| OrderID | Line Revenue | Line Cost | Margin % |
|---|---|---|---|
| 50001 | 96.00 | 46.00 | 52.1% |
| 50002 | 49.50 | 31.00 | 37.4% |
| 50003 | 75.00 | 37.20 | 50.4% |
| 50004 | 112.10 | 56.00 | 50.0% |
| 50006 | 99.50 | 47.00 | 52.8% |
| 50007 | 180.00 | 99.20 | 44.9% |
Each value is fixed after refresh. If a user picks "Cleaning" in a slicer, the Margin % on row 50003 is still 50.4%. That is fine for a row-level fact. It is a problem the moment you put that column in a visual and let Power BI add it up.
Measures: one value per cell of the visual
A measure has no row context. It is evaluated in filter context: the set of filters coming from the visual's rows, slicers and page filters. Write the margin as a measure instead:
Revenue = SUM ( Sales[Line Revenue] )
Cost = SUM ( Sales[Line Cost] )
Margin % = DIVIDE ( [Revenue] - [Cost], [Revenue] )| Category | Revenue | Cost | Margin % (measure) | Margin % column, summed | Margin % column, averaged |
|---|---|---|---|---|---|
| Coffee & Kitchen | 195.50 | 93.00 | 52.4% | 104.8% | 52.4% |
| Paper & Print | 161.60 | 87.00 | 46.2% | 87.4% | 43.7% |
| Cleaning | 255.00 | 136.40 | 46.5% | 95.3% | 47.6% |
| Total | 612.10 | 316.40 | 48.3% | 287.6% | 47.9% |
Only the measure is right. When you drag a numeric column into a visual, Power BI sums it by default, which gives a 104.8% margin. Switching the aggregation to Average looks plausible but is still wrong: it gives each order line equal weight, so the 49.50 copy paper line counts as much as the 112.10 toner line. The measure divides total margin by total revenue for each cell, which is the definition of margin.
Power BI DAX measure vs calculated column: side by side
| Calculated column | Measure | |
|---|---|---|
| When it is computed | At data refresh | When a visual is rendered |
| Evaluation context | Row context (one row) | Filter context (the visual's filters) |
| Stored in the model | Yes, uses memory | No, only the formula |
| Responds to slicers | No | Yes |
| Can go on slicers, rows, legends | Yes | No |
| Typical use | Categories, flags, keys, buckets | Totals, ratios, averages, YTD, % of total |
On performance: a calculated column costs memory and refresh time on every row, even if no visual uses it. A measure costs nothing until it is queried. For large fact tables, many columns are usually the bigger problem.
When to use a calculated column
Use a calculated column when the value belongs to the row and you want to slice, filter or group by it:
- A bucket such as an order size band that should appear on a chart axis or in a slicer.
- A flag used as a filter, such as
Is Completed = Sales[Status] = "completed". - A key for a relationship, or a sort-by column (for example Month Number to sort Month names).
- A value pulled from another table with
RELATEDthat you want to show on a row.
Order Size =
IF ( Sales[Quantity] >= 6, "Bulk", "Standard" )Now "Order Size" can sit on the rows of a visual, and the Revenue measure splits by it. For more branching logic see the DAX IF statement and SWITCH.
Before you write a calculated column, ask whether it can be built in Power Query instead. A column added during data load is usually compressed better and keeps your model tidy. The trade-offs are in DAX vs Power Query.
If you do not need the value per row, skip the column. Line Revenue above can be replaced with an iterator so nothing is stored:
Revenue =
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct]) )The iterator creates its own row context, computes each line, then sums. See SUM vs SUMX for when that matters.
Common mistakes
Storing a ratio in a calculated column
This is the margin example above. Ratios, percentages and averages must be measures, because they have to be recomputed for every group. If a report shows a margin above 100% or a share column that adds up to more than 100%, look for a ratio column being summed.
An AI assistant puts an aggregate inside a calculated column
Ask an AI assistant for "each line's share of revenue" and it often writes this as a calculated column:
Share of Revenue = Sales[Line Revenue] / SUM ( Sales[Line Revenue] )It runs without error. The trap: in a calculated column there is no filter context from your report, so SUM ( Sales[Line Revenue] ) always returns the total of the whole table. Line 50007 shows 29.4% (180.00 / 612.10) no matter what the slicer says. Filter the page to Cleaning and the shares still add up to 41.7%, not 100%.
How to spot it: change a slicer and watch whether the value moves. If it should move and does not, the logic is in a column. The fix is a measure that reads the filter context, for example with ALLSELECTED:
Share of Revenue =
DIVIDE ( [Revenue], CALCULATE ( [Revenue], ALLSELECTED ( Products[Category] ) ) )CALCULATE and ALL are explained in DAX CALCULATE explained.
Writing a measure that refers to a bare column
The reverse mistake. A measure like = Sales[Quantity] * Sales[UnitPrice] fails with "A single value for column 'Quantity' in table 'Sales' cannot be determined." A measure has no current row, so it needs an aggregation (SUM, MAX) or an iterator (SUMX).
Using implicit measures
Dragging a column into a visual creates an implicit measure (Sum of Line Revenue). It works, but you cannot reuse it in other formulas and the default aggregation may be wrong. Write explicit measures and hide the raw numeric columns.
FAQ
What is the difference between a measure and a calculated column in Power BI?
A calculated column is computed per row at refresh and stored in the table. A measure is computed at query time for the current filter context. Columns are for slicing and grouping, measures are for aggregating.
Is a measure or calculated column better for performance?
Usually a measure. Calculated columns take memory and refresh time on every row. Measures only use CPU when a visual asks for them. A column can be faster when it replaces a very expensive per-row calculation you filter on often.
Can I use a measure in a slicer?
Not directly. Slicers and axes need columns. If you need to slice by a measure result (for example customer revenue bands), create a calculated column or a separate table that holds the bands.
Why does my calculated column not change with the slicer?
Because it was computed at refresh, before any slicer existed. Move the logic to a measure.
Can a calculated column reference a measure?
Yes. The measure is evaluated for each row through context transition, so it returns the value for that row's filter. This can be slow on big tables, so use it sparingly.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Revenue by signup year” →