Measure vs calculated column
| Evaluated | Use when | |
|---|---|---|
| Calculated column | Once, at refresh, row by row | You need to slice or filter by the value |
| Measure | At query time, in filter context | You need to aggregate |
When in doubt, write a measure. Columns cost memory whether used or not.
The aggregation pair
Total Sales = SUM ( Sales[Amount] )
Total Sales = SUMX ( Sales, Sales[Quantity] * Sales[Price] )SUM adds up one existing column. SUMX walks the table row by row evaluating
an expression, then adds the results. Use SUMX when the thing you want does
not exist as a column.
CALCULATE
Sales Last Year =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )CALCULATE changes the filter context an expression is evaluated in. Almost
every non-trivial measure is a CALCULATE.
Time intelligence
Sales YTD = CALCULATE ( [Total Sales], DATESYTD ( 'Date'[Date] ) )
Sales MTD = CALCULATE ( [Total Sales], DATESMTD ( 'Date'[Date] ) )
Sales PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
YoY % = DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] )All of these need a real date table, contiguous, marked as a date table.
Removing filters
% of Total = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Product ) ) )ALL clears filters entirely. ALLSELECTED keeps what the user chose in
slicers but clears the visual's own context — usually what you actually want for
a "% of total" in a chart.
Always DIVIDE
Margin % = DIVIDE ( [Profit], [Sales] )DIVIDE returns blank on divide-by-zero. / returns an error that propagates
through everything downstream.