3.2.4. Statistical Functions and Semi-Additive Measures

💡 First Principle: Not every number can be summed. Averages, medians, and spreads describe distributions and must be computed over the underlying rows, not added up. Balances and inventory levels are snapshots in time: they add across stores or accounts but not across days. Both kinds of measures need aggregation logic that respects what the number means.

Statistical functions in DAX:

• AVERAGE, AVERAGEX for means; MEDIAN, MEDIANX for the middle value (robust to outliers).
• MIN/MAX and MINX/MAXX for extremes.
• STDEV.S / STDEV.P for sample and population standard deviation; VAR.S / VAR.P for variance.
• PERCENTILE.INC / PERCENTILE.EXC and their X variants for percentiles.
• COUNT, DISTINCTCOUNT and RANKX for ranking: Product Rank = RANKX(ALL(Product[Name]), [Total Sales]).

Use iterator versions when the statistic should be computed over an intermediate grain: average sales per customer is AVERAGEX(VALUES(Customer[CustomerKey]), [Total Sales]), which differs from AVERAGE(Sales[SalesAmount]) (average per transaction line).

Semi-additive measures handle snapshot facts. An Inventory table with one row per product per day records units on hand. Summing across products for a single day is correct; summing across 30 days of a month gives a meaningless number 30 times too large. A semi-additive measure returns the value at the last (or first) date in the period:

Units On Hand =
CALCULATE (
    SUM ( Inventory[Units] ),
    LASTDATE ( 'Date'[Date] )
)

Units On Hand (last date with data) =
CALCULATE (
    SUM ( Inventory[Units] ),
    LASTNONBLANK ( 'Date'[Date], CALCULATE ( SUM ( Inventory[Units] ) ) )
)

Closing Balance Month = CLOSINGBALANCEMONTH ( SUM ( Accounts[Balance] ), 'Date'[Date] )

LASTDATE returns the last date in the current context, even if that date has no snapshot (giving blank for the current incomplete month). LASTNONBLANK finds the last date that has data, which is usually what users expect. CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR, and OPENINGBALANCE* are shortcuts for period-end and period-start values. Averages over time (AVERAGEX(VALUES('Date'[Date]), [Units])) are another semi-additive option when the business wants average stock.

⚠️ Exam Trap: A balance measure that shows correct values on each day but a huge number at the month total is a summed snapshot. The fix is a semi-additive measure (LASTNONBLANK or CLOSINGBALANCEMONTH), not changing the visual's total setting or the column's default summarization to Average.

Reflection Question: Why does LASTDATE return blank for the current month in an inventory report loaded through yesterday, and how does LASTNONBLANK avoid that problem?