Calculate at the grain and context the business question requires

A calculated measure is an executable business definition evaluated under query context. It is not merely arithmetic placed in a dashboard. Correctness depends on source grain, relationship paths, filter context, row context, aggregation behavior, time semantics, null or blank rules, security scope, and the total being requested. The same expression can be correct per row and wrong at a subtotal because totals evaluate the formula in a broader context rather than adding the visible cells.

Prefer measures for results that should respond to slicers, grouping, and query filters. Calculated columns are computed for each model row at refresh and stored; they can support relationships, sorting, or static categories but increase model size and do not recalculate from report filter context. Power Query or upstream transformations are often better for deterministic row attributes. Choose placement from grain, refresh, reuse, folding, security, and performance—not from which editor is convenient.

DAX uses filter context and row context. Visuals, slicers, relationships, and explicit filters establish filter context. Iterators such as SUMX create row context over a table. CALCULATE evaluates an expression in modified filter context and performs context transition when needed. A measure reference inside an iterator generally receives the current row through implicit CALCULATE; a raw column aggregation and a measure can therefore behave differently. Make context changes explicit enough to review.

Aggregation must follow the business algebra. Additive measures can sum across supported dimensions. Balances are often semi-additive across accounts but require an end-of-period rule across time. Ratios should aggregate their numerator and denominator before division, not average percentages unless equal weighting is the intended metric. Distinct counts cannot be summed across overlapping groups. Medians, percentiles, and averages need population, weighting, and blank rules.

Iterators are necessary when an expression must be evaluated at a particular row grain—for example quantity times unit price after line-specific discount—then aggregated. They are not a universal replacement for simple storage-engine aggregations. Iterate the smallest correctly filtered table, reuse measures and variables, and avoid materializing huge intermediate tables. Validate both numerical equivalence and query plans or timing at realistic cardinality.

Filter modification is powerful and hazardous. CALCULATE filters may replace an existing filter on the same column unless combined appropriately. ALL, REMOVEFILTERS, ALLEXCEPT, and KEEPFILTERS encode different intent. A percentage-of-total denominator should remove only the dimensions meant to be ignored while retaining tenant, date, product, or security scope as required. A denominator that removes every filter may leak or misstate global data.

Relationships determine propagation. Cardinality, active versus inactive paths, direction, many-to-many bridges, and referential integrity affect every measure. USERELATIONSHIP can select an alternate date role for a calculation but does not fix an ambiguous model. Broad bidirectional filters can produce surprising totals and security paths. Design a star schema with deliberate relationship roles and test measures by each supported dimension combination.

Time intelligence requires a trustworthy calendar and explicit business semantics. Define event date, reporting timezone, fiscal periods, week start, incomplete periods, period close, late corrections, and comparison logic. Prior-period and year-over-year measures need comparable day coverage and leap-year behavior. Avoid treating a built-in time function as a substitute for a business calendar.

Blank, zero, infinity, error, and suppressed values are distinct. DIVIDE can handle zero denominators predictably, but the alternate result must match the metric contract. Returning zero for no eligible population invents a measured outcome; returning blank may be correct and lets visuals suppress irrelevant groups. Preserve true zeros, expose data-quality failures separately, and do not use error swallowing to hide broken relationships or types.

Security is evaluated with the model. Row-level security and object access must apply before measures, drill, exports, subscriptions, caches, and composite queries. Calculations can infer protected values through totals or differences. Do not use a measure returning BLANK as the only access control if the underlying field remains queryable. Test roles with representative identities and verify workspace or administrative bypass semantics.

Treat calculations as versioned code. Give measures owner, definition, format, unit, grain, inputs, time and filter behavior, examples, tests, certification, lineage, and deprecation. Test leaf values, subtotals, grand totals, empty sets, one and many rows, duplicate keys, relationship changes, cross-filtering, security, time boundaries, and performance. Reconcile important measures to trusted independent totals and compare old and new definitions before release.

Additivity is the property that decides whether a measure can be aggregated at all, and it is the source of most wrong totals in a report. A fully additive measure such as revenue sums correctly across every dimension. A semi-additive measure such as an account balance or an inventory level sums across product and region but not across time, where the correct aggregation is a period-end or period-average value. A non-additive measure such as a ratio, a percentage, or a distinct count cannot be summed at all: the total margin percentage is not the sum of the row percentages, and a distinct customer count across two regions is not the sum of the regional counts unless no customer appears in both. Declare the additivity of every measure in the model rather than leaving the aggregation to whichever visual the user drops it into, and where a measure is non-additive, define the total explicitly so the grand total is computed rather than accumulated.

Ratios must be computed at the level they are reported, not averaged from a lower one. A conversion rate of 5 percent in one segment and 40 percent in another does not give 22.5 percent overall; the correct figure divides total conversions by total sessions and lands wherever the segment volumes put it. The same applies to any measure with a denominator — cost per unit, revenue per user, defect rate — and the error is invisible when the segments happen to be similar in size, which is why it survives review and surfaces in a board pack. Write the ratio as a division of two additive measures rather than as an aggregation of a stored ratio column, and test the total against a hand calculation on a small fixture where the segment sizes deliberately differ.

Filter context is what makes the same expression return different numbers in different places, and it is the part candidates most often cannot explain. A measure evaluates against whatever filters the visual, the slicers, the row context, and any cross-filtering relationships apply, so the same measure legitimately returns a different value in a total row than in the detail rows above it. Where a calculation must ignore or override part of that context — a share-of-total, a prior-period comparison, a running balance — the override belongs in the measure explicitly rather than being achieved by arranging the report a particular way, because a report arrangement is not a contract and the next person will rearrange it.