SUM vs SUMX (row context vs filter context)

1. The problem

Revenue = Qty × UnitPrice × (1 − Discount) — for each row. Summing first and multiplying later is wrong:

(The same trap as pivot calculated fields in Module 2.)

2. SUM vs SUMX

• SUM(column) adds up one existing column. Fast and simple.
• SUMX(table, expression) goes row by row through the table, evaluates the expression for each row, then adds the results.

Total Revenue X := SUMX(fact_Sales, fact_Sales[Qty] * fact_Sales[UnitPrice] * (1 - fact_Sales[Discount]))
Total Cost X    := SUMX(fact_Sales, fact_Sales[Qty] * RELATED(dim_Products[UnitCost]))

Same results as the calculated columns in Lesson 2 — without storing two extra columns for 50,000 rows. Measures stay small; the model stays fast.

3. Two contexts

In a measure, writing fact_Sales[Qty] alone (no SUM, no X-function) is an error: "A single value for column 'Qty' cannot be determined" — there's no current row. SUMX creates one.

In a calculated column you are in a row, so fact_Sales[Qty] * fact_Sales[UnitPrice] works directly.

4. Other X-functions

Avg Line Value  := AVERAGEX(fact_Sales, fact_Sales[Qty] * fact_Sales[UnitPrice])
Biggest Order   := MAXX(fact_Sales, fact_Sales[Qty] * fact_Sales[UnitPrice] * (1 - fact_Sales[Discount]))
Avg Store Revenue := AVERAGEX(VALUES(dim_Stores[StoreName]), [Total Revenue])

The last one iterates over stores, not sales rows: for each store it computes Total Revenue (the measure gets that store as its filter), then averages — "average revenue per store". Iterating over a dimension with a measure is a very common pattern.

5. Performance notes

• SUMX over 50,000 rows is instant; over tens of millions it's still fine for simple arithmetic.
• RELATED inside SUMX is efficient thanks to relationships.
• Avoid FILTER(fact_Sales, …) inside SUMX when a CALCULATE column filter would do.

6. Which to use?

Common mistakes

SUM(Qty) × SUM(Price). Writing a bare column in a measure. Creating many calculated columns only to sum them (use SUMX instead).