Power BI / Basic: analytical and modeling foundations / Lesson 5

Measures, aggregation and ratios

Define explicit DAX measures that aggregate at the correct grain and recompute meaningful ratios in each filter context.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Distinguish a measure from a stored calculated column.
Choose SUM and SUMX according to the expression grain.
Explain ratio totals and zero-denominator behavior.

Choose the calculation role

A measure evaluates a scalar result in the current query/filter context. A calculated column produces a row-level value stored during model processing in the import-model teaching assumption. Use a measure for a dynamic total or ratio; use a column when a stable row attribute is needed for grouping or relationships and its cost is justified. A calculated column does not recalculate separately for each slicer selection. Explicit measures also communicate metric ownership better than relying on whichever implicit aggregation a visual happens to choose for a numeric column.

Aggregate at line grain

If FactSales already contains validated LineAmount, SUM can add that column. If only Quantity and UnitPrice exist, SUMX iterates included rows and sums their products. The first snippet assumes the three-line fixture and no additional filters, producing intended revenue70. Multiplying total quantity by a summed or averaged price answers a different question when prices vary. DAX snippets remain unexecuted; their expected results are derived from the documented fixture. Preserve the distinction between a measure definition and a complete model or DAX query that could actually evaluate it.

dax Explicit line-revenue and ratio measures

Revenue = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPrice])
Units = SUM(FactSales[Quantity])
Average Unit Revenue = DIVIDE([Revenue], [Units])

With quantities2,1,3 and prices10,20,10, intended Revenue is70, Units6 and Average Unit Revenue70/6. These definitions were not evaluated by DAX.

Recompute ratios at total context

A total ratio should usually divide total numerator by total denominator under that total’s context, rather than sum displayed row ratios. The second program compares weighted and unweighted conversion rates. One group has1 success from2 opportunities and another9 from90. The overall rate is10/92, not the average of50% and10%. A matrix grand total reevaluates the measure for its own context; it is not necessarily arithmetic over visible cells. Decide the business contract first, because an equally weighted group average can be valid for another question and should be named accordingly.

python Exact synthetic ratio comparison

from fractions import Fraction
groups = [(1, 2), (9, 90)]
overall = Fraction(sum(a for a, _ in groups), sum(b for _, b in groups))
unweighted = sum((Fraction(a, b) for a, b in groups), Fraction(0)) / len(groups)
print(overall)
print(unweighted)

Expected output

5/46
3/10

5/46 is the overall opportunity rate;3/10 is the unweighted group average. Both are exact arithmetic with different meanings.

Decide blank and zero

DIVIDE provides a defined alternative for a zero denominator, defaulting to BLANK when an alternative is not supplied. A blank can communicate no meaningful ratio, while zero can communicate a genuine zero result. Do not replace blanks with zero merely to fill a card without considering interpretation. The alternate result has a constant requirement documented by the function. Empty selections and missing rows also need explicit cases. Format a ratio as a percentage only after its numeric meaning is correct; appending a percent sign to an already multiplied value can create a hundredfold display error.

Test measures with small contexts

Review the default total, one product, a combination of filters, no matching rows and a zero-denominator group. Compare each result with an independent oracle and explain any total that differs from the visible row arithmetic. Use meaningful names and descriptions so a consumer knows whether revenue includes returns, tax or currency conversion. This course executes only the Fraction comparison and never opens Desktop or a semantic model. Actual DAX evaluation, blank handling in visuals and relationship propagation remain separate checks. Correct local arithmetic is a foundation for engine validation, not a substitute for it.

Common errors and fixes

A total is forced to sum row percentages

Why it happens: The ratio’s denominator meaning was ignored.
How to fix it: Define the intended weighted or group-average contract explicitly.
Prevent it next time: Keep a two-group unequal-denominator fixture.

A calculated column is expected to follow slicers

Why it happens: Stored row calculation was confused with context-time measure evaluation.
How to fix it: Use an appropriate measure for dynamic aggregation.
Prevent it next time: Choose calculation role before writing the expression.

A blank ratio is converted to zero without explanation

Why it happens: Presentation completeness replaced metric semantics.
How to fix it: Document no-data and zero-denominator behavior.
Prevent it next time: Test empty and actual-zero cases separately.

Check your understanding

1. Why is overall10/92 different from averaging1/2 and9/90?

Show worked solution

The overall rate weights every opportunity equally, while the average weights each group equally regardless of size. Both calculations are valid arithmetic but answer different questions. A business measure should name its weighting contract and recompute the corresponding numerator and denominator in the total context.

2. When should an existing LineAmount column use SUM instead of recomputing SUMX?