DAX SUMX: why row-level multiplication needs an iterator

Total line value is the sum of each line's quantity multiplied by that line's unit price. Multiplying total quantity by the sum of unit prices creates cross-products that do not represent the original transactions.

SUMX provides a row-by-row expression followed by aggregation. The key is choosing an input table at the correct grain and applying discounts, costs and eligibility according to their definitions.

Inspect a two-line counterexample

In the retail lab, order O1 contains two lines. S1 is two units at 10,000 paise and S2 is one unit at 5,000. Gross value is 2×10,000 + 1×5,000 = 25,000.

Multiplying summed quantities by summed prices gives (2+1)×(10,000+5,000) = 45,000. The extra 20,000 comes from combining each quantity with prices from other lines. No transaction supports those cross-products.

This small counterexample is more revealing than testing a single line, where both expressions happen to agree.

Write the row-level aggregation

Gross Paise =
SUMX(FactSales, FactSales[Quantity] * FactSales[UnitPricePaise])

SUMX evaluates the expression for each row of the table in the current context, then sums it. Microsoft describes the function in its SUMX reference.

For net value, subtract the line discount inside the row expression:

Net Paise =
SUMX(
    FactSales,
    FactSales[Quantity] * FactSales[UnitPricePaise]
        - FactSales[DiscountPaise]
)

O1's net value is 24,000 because its combined line discount is 1,000. DiscountPaise is already a line total, so multiplying it by Quantity would apply the discount too many times.

Apply the intended status population

Paid Gross Paise = CALCULATE([Gross Paise], FactSales[Status] = "Paid")
Paid Net Paise = CALCULATE([Net Paise], FactSales[Status] = "Paid")

The Paid population contains ten units across seven lines. Its gross value is 72,000, total discount 2,500 and net value 69,500 paise.

The deliberately incorrect product of Paid total quantity ten and summed Paid unit-price values 52,000 would give 520,000 gross paise. Subtracting the correct 2,500 discount afterward still leaves a wrong 517,500 net result.

Correct discount arithmetic cannot repair an incorrect multiplication grain.

Ensure the iterator's rows are the intended rows

SUMX does not automatically detect duplicate source lines or a table expression expanded through a one-to-many merge. If the same business line appears twice in the iterator input, its value can be counted twice.

Validate LineID uniqueness and relationship or merge assumptions before trusting the total. Use a separately aggregated table only when its grain and available fields support the expression you intend to calculate.

Do not iterate over customers and multiply customer total quantity by an unqualified price measure unless that is a deliberately defined customer-level calculation. Moving the iterator to a different table changes the question.

Compare with a prepared line-value column

If a reliable source already provides net line value under the same contract, SUM of that column may be sufficient. A calculated Line Net Paise column plus SUM can also reproduce this fixture's result in an Import model.

Choose based on reuse, model size, source responsibility and measured performance. An iterator is needed here to express the row calculation at query time; it is not a rule that every total must use SUMX.

Test with quantities that differ

Use at least two lines with different quantities and prices, a nonzero line discount and a status exclusion. Compare per-line outputs, group totals and the grand total. A dataset where every quantity is one can make a flawed approach look more plausible.

The measure contracts provide consistent baseline definitions. The fixture's SQLite controls verify arithmetic; DAX evaluation remains a Power BI application-review step.

Exercise: split S1 into two one-unit lines, allocating its 1,000-paise discount as 500 per line. The correct gross and net totals should remain unchanged. Explain which key and order-count controls need updating after the row representation changes.

NeuraPath's Data Analytics with Generative AI course connects DAX iteration with business grain. A correct SUMX measure explains what each row represents before showing the total.

Continue learning

This article is part of the DAX measures and analytical correctness sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in CALCULATE explained through a changing filter context.
• Continue with Use DIVIDE to make undefined business ratios explicit.