Why row-wise sales calculations matter in Power BI

Suppose two fictional orders contain 2 units at $10 and 3 units at $4. Their sales amounts are $20 and $12, totaling $32. The quantity and price must be paired within each row before the results are combined.

In DAX, Sales = SUMX(Orders, Orders[Units] * Orders[UnitPrice]) expresses that row-wise calculation. SUMX evaluates the expression for each relevant row and adds the results. SUM(Orders[Units]) * SUM(Orders[UnitPrice]) instead gives 5×14 = $70, which mixes quantities and prices across orders.

A measure is evaluated in filter context. Selecting a region or category changes the rows contributing to the measure. Check both the full total and filtered values. A correct grand total alone does not prove that relationships or visual interactions work.

For average order value, divide Sales by a distinct order count that matches the source grain. For these two orders, $32/2 is $16. Dividing by five units gives $6.40 per unit, a different measure. Use clear labels and an appropriate missing or zero-denominator policy; DIVIDE can return blank when the denominator is zero.

AI can help draft a measure, but test the proposed expression against a tiny known-result dataset. A formula that executes successfully can still answer the wrong business question. READY’s Power BI course includes the supplied files, model, measures, and test matrix needed to practice that distinction.

Continue with the full course

Build a Power BI report from supplied data. Learn Power Query, relationships, DAX measures, visuals, refresh checks, and responsible AI-assisted review.

This is original, independent READY training. READY is not affiliated with or endorsed by Microsoft. Course completion does not confer a provider credential.