Iterator Functions

// SUM on a stored column — simple aggregation
Total Revenue (SUM) = SUM(Orders[Revenue])
// Adds the pre-computed Revenue column: 1400+1125+950+80+1680 = 5,235

// SUMX computing revenue per row from component columns
Total Revenue (SUMX) =
    SUMX(
        Orders,
        Orders[UnitPrice] * Orders[Quantity] * (1 - Orders[Discount%])
    )
// Phase 1 — per row:
//   1001: 350 × 4 × 1.00 = 1,400
//   1002: 250 × 5 × 0.90 = 1,125
//   1003: 500 × 2 × 0.95 =   950
//   1004:  80 × 1 × 1.00 =    80
//   1005: 280 × 6 × 1.00 = 1,680
// Phase 2 — sum: 1400+1125+950+80+1680 = 5,235

// Both return 5,235 here — SUMX matches SUM because Revenue
// was computed using the same formula in Power Query.

// Scenario: Average revenue per unit across all orders

// WRONG — average of the Revenue column divided by average Quantity
// This is (avg revenue) / (avg quantity) — mathematically incorrect
Avg Rev Per Unit WRONG =
    DIVIDE(AVERAGE(Orders[Revenue]), AVERAGE(Orders[Quantity]))
// = DIVIDE(1047, 3.6) = 290.8  ← wrong

// CORRECT — AVERAGEX computes revenue/unit per row, then averages those
Avg Rev Per Unit =
    AVERAGEX(
        Orders,
        DIVIDE(Orders[Revenue], Orders[Quantity])
    )
// Phase 1 — per row:
//   1001: 1400 / 4 = 350.0
//   1002: 1125 / 5 = 225.0
//   1003:  950 / 2 = 475.0
//   1004:   80 / 1 =  80.0
//   1005: 1680 / 6 = 280.0
// Phase 2 — AVERAGE of {350, 225, 475, 80, 280}
//         = 1410 / 5 = 282.0  ← correct row-level average

// Pattern: SUMX over a dimension table to aggregate a measure

// Scenario: total revenue only for orders where the customer is Premium
// (Customer tier lives in the Customers table, not Orders)

// Naive attempt — WRONG, cannot filter across relationship in a simple SUM
// Premium Revenue = SUM(Orders[Revenue]) -- returns all revenue, ignores tier

// Correct: SUMX iterates Customers, filters to Premium, sums their Orders revenue
Premium Revenue =
    SUMX(
        FILTER(Customers, Customers[Tier] = "Premium"),
        CALCULATE(SUM(Orders[Revenue]))
        -- CALCULATE triggers context transition: each Customers row
        -- becomes a filter context that restricts Orders to that customer
    )

// Pattern: weighted average margin using SUMX
// Margin = (Revenue - Cost) / Revenue, weighted by Revenue
Weighted Avg Margin % =
    DIVIDE(
        SUMX(Orders, Orders[Revenue] - Orders[Cost]),
        SUM(Orders[Revenue])
    )
// = total gross profit / total revenue = correct weighted margin
// Compare: AVERAGEX(Orders, DIVIDE([Revenue]-[Cost],[Revenue]))
//          gives equal-weight average margin per order — different result

// SUMX — sum of per-row expressions
Total Gross Profit =
    SUMX(Orders, Orders[Revenue] - Orders[Cost])

// AVERAGEX — average of per-row expressions
Avg Gross Profit Per Order =
    AVERAGEX(Orders, Orders[Revenue] - Orders[Cost])

// MINX — minimum of per-row expressions
Min Order Margin =
    MINX(Orders, DIVIDE(Orders[Revenue] - Orders[Cost], Orders[Revenue]))

// MAXX — maximum of per-row expressions
Max Order Margin =
    MAXX(Orders, DIVIDE(Orders[Revenue] - Orders[Cost], Orders[Revenue]))

// COUNTX — count rows where expression is not blank
Orders With Margin =
    COUNTX(Orders, IF(Orders[Revenue] > Orders[Cost], 1, BLANK()))

// PRODUCTX — multiplies all per-row results (rarely used outside finance)
Compound Growth =
    PRODUCTX(
        GrowthRates,
        1 + GrowthRates[MonthlyRate]
    )  -- multiplies (1 + rate) for each month = compound growth factor

// RANKX(table, expression, value, order, ties)
// table: the set of items to rank across
// expression: what to rank by (evaluated for every row in table)
// value: the value to rank (blank = use current context)
// order: DESC (1=highest) or ASC (1=lowest)
// ties: SKIP (default gaps) or DENSE (no gaps)

// Rank products by revenue — highest = rank 1
Product Revenue Rank =
    RANKX(
        ALLSELECTED(Products[ProductName]),
        [Total Revenue],
        ,       -- blank value = use measure in current context
        DESC,
        DENSE   -- no gaps: 1,2,3 not 1,2,4 if tie at rank 2
    )

// Rank regions by order count — lowest count = rank 1
Region Order Count Rank =
    RANKX(
        ALLSELECTED(Orders[Region]),
        COUNTROWS(Orders),
        ,
        ASC,
        DENSE
    )