Free tools Windows power users keep installed
One-click scans. No signup required.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
SUM totals numbers that already exist in one column; SUMX evaluates an expression for every row of a table and then totals those row results. That distinction determines whether a measure such as SUM ( Sales[SalesAmount] ) is sufficient or whether you need an iterator such as SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] ). Use SUM for a stored amount, and use SUMX when the amount has to be derived, conditioned, or retrieved from a related table at row level. SUMX also establishes row context, while report filters, slicers, and relationships supply filter context. The sections below show how those contexts interact, when CALCULATE matters, and which deployment limitations to check.
What is the difference between SUM and SUMX in DAX?
SUM is a direct column aggregator. Its documented contract is one column reference, and it adds the numeric values stored in that column. SUMX is an iterator: it receives a table (or table expression), evaluates an expression for each row, and sums the resulting values. Microsoft describes SUMX as returning “the sum of an expression evaluated for each row in a table.”
That makes the choice depend on where the value comes from:
| Question | Use SUM | Use SUMX |
|---|---|---|
| What is the input? | One numeric column, such as Sales[SalesAmount]. |
A table plus an expression, such as Sales and Sales[Quantity] * Sales[Unit Price]. |
| At what grain is work done? | Values already stored in the column are aggregated. | The expression is evaluated at each input-row grain before aggregation. |
| What context is available? | Usually the surrounding filter context. | The surrounding filter context plus row context created by iteration. |
| Typical use | Total an existing amount, count-like numeric total, or other ready-to-add column. | Calculate extended amounts, conditional row logic, or values obtained from a related row. |
The official references are Microsoft Learn’s SUM documentation and SUMX documentation. SUM does not accept a table-plus-expression pattern, so writing a multiplication expression inside SUM is not the equivalent of SUMX.
#1 Best Overall
Why does quantity multiplied by price need SUMX?
When each sales line has a quantity and a unit price, revenue is not stored as one value that can be directly summed. DAX must first multiply the two fields for each visible line, then add those products. SUMX expresses that evaluation order explicitly.
Revenue =
SUMX (
Sales,
Sales[Quantity] * Sales[Unit Price]
)For a row with quantity 3 and unit price 12, the row expression produces 36. SUMX repeats that calculation for every row in its table argument and adds the products. If a report filters the table to a month, customer, or product, only rows remaining in the filter context participate.
This is different from trying to write:
-- Not the documented SUM pattern
Total = SUM ( Sales[Quantity] * Sales[Unit Price] )SUM expects a column reference, not a table and row expression. If your model already has a correctly calculated Sales[SalesAmount] column, the equivalent measure is simpler:
Total Sales = SUM ( Sales[SalesAmount] )Do not calculate the same amount twice merely to use SUMX. Choose the function that matches the shape of the data you actually need to aggregate.
How do row context and filter context affect SUMX?
SUMX creates a current-row, or row, context while it scans its table. Filter context is the set of filters supplied by a visual, slicer, page, relationships, or the formula itself. The table passed to SUMX is first evaluated under the active filter context; the expression then runs once for each row in that table.
Microsoft’s DAX overview describes row context as “the current row.” In practical terms, references such as Sales[Quantity] and Sales[Unit Price] resolve to the values on the row currently being processed. A matrix grouped by product therefore causes the same measure to iterate a different visible subset for each product, while a date slicer changes the subset again.
These contexts explain why a measure can return different results without changing its definition. The expression stays the same; the report changes which rows are visible. Row context is local to the iterator, whereas filter context comes from the evaluation environment around the measure.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Rank #2
How do I total an amount that is already stored?
Use SUM when one column contains the exact numeric amount to add. The measure is short, readable, and directly reflects the model:
Total Sales = SUM ( Sales[SalesAmount] )SUM is appropriate when the data-loading or transformation process has already produced the amount at the required grain. It also remains responsive to the report’s active filters, so the measure can be placed in cards, charts, or tables without a separate row-by-row expression.
Before choosing SUM, verify that the column represents one additive value per row. If the stored field is a unit price, quantity, percentage, or another component that must be combined with a second field, summing it directly answers a different question than total revenue or total cost.
What SUMX patterns are useful beyond quantity times price?
SUMX is useful whenever each row needs its own calculation before the final total. The table argument can be a model table or a table expression that defines the rows to scan.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesConditional row amounts
You can apply row-level business logic inside the expression, then aggregate the result:
Discounted Revenue =
SUMX (
Sales,
IF (
Sales[Quantity] >= 10,
Sales[Quantity] * Sales[Unit Price] * 0.90,
Sales[Quantity] * Sales[Unit Price]
)
)Each row receives its own branch of the condition. This is not the same as applying one discount to the already aggregated revenue, because the threshold is evaluated separately for every line.
Using a value from a related table
When the per-row expression needs a column from a related table, RELATED can retrieve that value while SUMX supplies row context:
Rank #3
Extended Cost =
SUMX (
Sales,
Sales[Quantity] * RELATED ( Products[Unit Cost] )
)Microsoft documents that RELATED requires row context and can be nested in a table-scanning function such as SUMX. The relationship must be configured so that the product row can be found from the current sales row; otherwise the lookup cannot provide the intended unit cost. See the RELATED function reference for its row-context requirement.
Iterating a deliberately shaped table
Sometimes the rows to evaluate should be selected or transformed before the expression runs. In that case, pass an appropriate table expression as SUMX’s first argument rather than the entire base table. This lets the filter or grouping logic define the input set while the second argument still performs the row-level calculation.
When do I need CALCULATE inside an iterator?
You need to think about CALCULATE when the expression inside SUMX must evaluate another model aggregation as though the current row were a filter. CALCULATE performs context transition in this situation: it converts the current row context into filter context for the expression being evaluated.
For example, an iterator may need to obtain a measure-like result for each row, rather than merely multiply columns from that row. Microsoft explains that context transition is required when an expression (rather than a model measure) that summarizes model data is evaluated in row context, including inside an iterator. The relevant reference is Microsoft Learn’s CALCULATE documentation.
Do not wrap every SUMX expression in CALCULATE automatically. A direct arithmetic expression such as Sales[Quantity] * Sales[Unit Price] already reads the current row and does not need context transition. Add CALCULATE when the nested expression must see that row as a filter on model data.
Recommended Free Tools
What does the evaluation order look like?
Understanding the order prevents many DAX mistakes:
- Establish the surrounding filter context. Visual filters, slicers, relationships, and formula filters determine which rows are visible.
- Evaluate the table argument. SUMX receives that visible table or table expression.
- Create row context. The iterator moves through the input one row at a time.
- Evaluate the expression. Column references resolve against the current row; related lookups can use that row context.
- Aggregate the row results. SUMX adds the values produced by the expression.
This sequence also explains why moving a filter into the table argument can change which rows are processed, while changing the second argument changes what is calculated for each processed row.
Does SUMX always create row context?
Yes. SUMX is classified by Microsoft as an iterator, and iterator functions establish row context over their table argument. That context is what allows a row expression to read columns from the current row and what enables functions such as RELATED to work in this pattern.
Row context does not automatically become filter context for every other calculation. If a nested aggregation needs the current row to act as a filter, use the context-transition behavior of CALCULATE as described above. Keeping those two contexts distinct is more reliable than treating “current row” and “active filters” as interchangeable.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
What are the practical differences when choosing between SUM and SUMX?
The following decision axes are useful when reviewing a measure or designing one from scratch:
| Axis | SUM | SUMX |
|---|---|---|
| Input shape | A single numeric column. | A table or table expression and an expression. |
| Calculation grain | Aggregates values already present in the column. | Calculates at row grain, then aggregates. |
| Row context | Does not provide an iterator’s per-row expression context. | Provides current-row context while scanning. |
| Best fit | Stored totals such as a prepared sales-amount column. | Derived totals, conditions, arithmetic across columns, and related-column lookups. |
| Syntax shape | SUM ( Table[Column] ) |
SUMX ( Table, Expression ) |
The functions are not competing versions of the same syntax. SUMX is the appropriate abstraction when there is an expression to evaluate per row; SUM is the direct abstraction when the values are already in one column.
Is SUMX slower than SUM?
There is no universal, authoritative percentage that makes SUMX a fixed amount slower or faster than SUM. The Microsoft function references define semantics and support limits, not a comparative benchmark across models, storage modes, data volumes, or expressions.
Performance depends on the table being scanned, the complexity of the expression, relationships and lookups involved, the model’s storage mode, and the filters active at query time. Start with the semantically correct function. If a measure is slow, inspect the actual model and query plan, reduce unnecessary rows in the table argument where appropriate, and simplify expensive row expressions rather than replacing SUMX with SUM when the calculation genuinely requires row-level work.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallMicrosoft’s DAX function reference is a broad catalog of more than 250 functions; that scope statement is not a SUM-versus-SUMX performance result.
Best Value
What is the DirectQuery limitation for SUMX?
Microsoft lists SUMX as unsupported in DirectQuery calculated columns and row-level security (RLS) rules. That restriction concerns those authoring locations; it is not a claim that every SUMX measure is invalid in every DirectQuery model. Check the support note for your specific model design before placing the function in a calculated column or RLS expression.
If the same business calculation is needed in a DirectQuery scenario, decide whether it belongs in the source query, a transformation layer, or a supported measure pattern. The correct alternative depends on the required security behavior and where the row-level amount can be computed without violating the mode’s restrictions.
How can I troubleshoot a SUM or SUMX result?
Use this sequence to isolate whether the issue is syntax, data shape, or context:
- Check the input type. SUM must receive a column. If you have arithmetic or conditional logic, move that logic into SUMX’s expression argument.
- Check the row grain. Confirm that one row represents the unit you intend to multiply, discount, or otherwise calculate.
- Check active filters. A slicer, visual grouping, relationship, or formula filter can legitimately change the table seen by the measure.
- Check relationships for RELATED. The current row must be able to reach the intended related row, and the lookup must be evaluated inside row context.
- Check context transition. If the expression performs another model aggregation and expects the current row to filter it, evaluate whether CALCULATE is required.
- Check deployment mode. SUMX in a DirectQuery calculated column or RLS rule is listed as unsupported.
Build a small diagnostic measure or visual that exposes the relevant columns and filters, then compare it with the iterator’s row expression. This usually reveals whether the unexpected total comes from the formula itself or from the rows supplied to it.
Which function should I choose?
Use this checklist before writing the measure:
- Is the exact amount already stored in one numeric column? Use
SUM ( Table[Amount] ). - Must you combine two or more columns for each row? Use SUMX, such as quantity multiplied by unit price.
- Does each row follow conditional business logic? Put that logic in SUMX’s expression.
- Must the expression retrieve a value from a related table? Use a row-context-aware lookup such as RELATED inside SUMX, with a valid relationship.
- Does the expression contain another model aggregation? Determine whether CALCULATE is needed for context transition.
- Will the formula live in a DirectQuery calculated column or RLS rule? Check the SUMX support limitation before deployment.
The concise rule is: SUM adds a column; SUMX adds the results of a per-row expression. Select the one that matches the calculation’s grain and context requirements, not a presumed performance ranking.
Frequently Asked Questions
Can SUMX use a filtered table instead of the whole fact table?
Yes. Its first argument can be a table expression, so the iterator can scan only the rows selected by that expression. The row calculation remains in the second argument, which keeps row selection separate from per-row arithmetic.
Why does the same SUMX measure change when I move it to another visual?
Each visual supplies its own filter context through groupings, slicers, relationships, and filters. SUMX evaluates its table argument inside that context, so a product row, a month row, and a grand total can expose different input rows.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →When is RELATED preferable to duplicating a lookup value in the fact table?
RELATED lets the iterator retrieve the related value while it is evaluating the current row, provided the model relationship supports the lookup. It is useful when the calculation naturally belongs in a measure and the related attribute should remain managed in its dimension table.
Can a measure containing SUMX be reused inside another measure?
Yes. A measure can be referenced by other measures, and its result is evaluated in the caller’s filter context. If a nested calculation needs the current iterator row to act as a filter, examine whether context transition with CALCULATE is required.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
