Calculated column vs measure in Power BI: which to use

Both are written in DAX and both end up in the Data pane, so the choice between a calculated column vs measure looks like a matter of taste. It isn't. They're worked out at different times, in different contexts, and they cost different things. Pick the wrong one and you get a bigger model, totals that don't add up, or a number that ignores the slicer.

The difference in one table

Row context means "the current row". A calculated column such as = [Freight] + [Tax] gets the values from the same row, one row at a time, and can follow relationships to related tables with RELATED.

Filter context is the set of rows a visual's cell is looking at. Each cell, each combination of row and column headers, and each slicer selection gives a measure a different subset of data, so the same formula can return a different answer in every cell.

The storage line is for Import models. In DirectQuery, Microsoft says calculated columns aren't stored and are worked out at query time instead.

When to use a calculated column

Use a column when you need a value per row that people will slice, filter or group by. A measure can't go on a slicer or on a visual's rows or axis.

A price band to slice sales by is a typical example. Right-click the Sales table in the Data pane, choose New column, and enter:

Price band =
SWITCH (
    TRUE (),
    Sales[Amount] < 100, "Under 100",
    Sales[Amount] < 500, "100 to 499",
    "500 and over"
)

Every row gets its band at refresh, and Price band works in a slicer like any other column.

Power Query is often the better place

A custom column in Power Query does the same job before the data enters the model. Microsoft's guidance for Import models prefers it: calculated columns usually compress less well, and they're only built once all the Power Query tables have loaded, which can lengthen the refresh. When the source is a database, Microsoft goes one step further and suggests doing the calculation in the SQL query, or storing it as a column in the source.

In Power Query Editor, select Add column > Custom column and enter:

if [Amount] < 100 then "Under 100"
else if [Amount] < 500 then "100 to 499"
else "500 and over"

In Power BI Desktop the Custom column dialog doesn't offer a data type, so set the new column's type afterwards.

Keep the column in DAX when its formula needs a measure, a related table, or a DAX-only function. A customer segment is a good example:

Customer segment = IF ( [Sales Amount] < 2500, "Low", "High" )

Added to the Customer table, this works out each customer's own sales: when you use a measure in a row context, DAX turns the current row into a filter for you (Microsoft calls this context transition). Remember the result is fixed at refresh. Slicing by year won't move a customer from Low to High.

When to use a measure

Use a measure for anything you add up, count, average, divide or compare over time: totals, ratios, percentages, year to date. Select Home > New measure (in the Calculations group) and the result recalculates for every cell, every slicer and every total.

Worked example: margin %

Say the Sales table has an Amount and a Cost for each order line. Here are two lines:

A Line margin % column gives each line its own margin. Summed in a visual, it shows 60%; averaged, 30%. Neither is the margin of the business, which is 150 profit on 1,100 sales: 13.6%.

The fix is to add up first and divide last, in measures:

Sales Amount = SUM ( Sales[Amount] )

Total Cost = SUM ( Sales[Cost] )

Margin % = DIVIDE ( [Sales Amount] - [Total Cost], [Sales Amount] )

Format Margin % as a percentage. Now each row of a visual, and the total, divides its own sums, so the total shows 13.6%. DIVIDE returns a blank instead of an error when the denominator is zero or blank, and Microsoft recommends it whenever the denominator could be either. Leave out the third argument: a blank lets visuals hide groups with no sales.

Implicit or explicit measures

Drag a numeric column into a visual and Power BI sums it for you. That's an implicit measure, shown with a sigma (∑) in the Data pane. A measure you write yourself is explicit, shown with a calculator icon.

Implicit measures are handy, but Microsoft gives reasons to write explicit ones even for a plain sum:

• Analyze in Excel uses PivotTables, which send MDX queries, and MDX needs explicit measures.
• Paginated reports built with the MDX query designer need them too.
• You control how a column is summarised. A unit price should never be summed, so hide the column and offer measures such as an average, minimum and maximum instead.

Explicit measures can also be reused inside other measures, as Margin % reuses Sales Amount above.

The quick decision

Common mistakes

For why totals behave the way they do, see why are my totals wrong. For where the columns should live in the first place, building a star schema from messy exports does most of the work in Power Query, and a date table in Power BI shows the measures that compare periods.

Related: Power BI DAX cheat sheet, Power Query vs Power Pivot vs Power BI.

Sources

• Use calculation options in Power BI Desktop — Microsoft Learn
• Create calculated columns in Power BI Desktop — Microsoft Learn
• Tutorial: Create calculated columns in Power BI Desktop — Microsoft Learn
• Create measures for data analysis in Power BI Desktop — Microsoft Learn
• Tutorial: Create your own measures in Power BI Desktop — Microsoft Learn
• DAX overview — Microsoft Learn
• CALCULATE function (DAX) — Microsoft Learn
• Understand star schema and the importance for Power BI — Microsoft Learn
• Data reduction techniques for Import modeling — Microsoft Learn
• Add a custom column — Microsoft Learn
• DIVIDE function (DAX) — Microsoft Learn
• DIVIDE function vs. divide operator (/) — Microsoft Learn