SUM adds up the values in one column. SUMX takes a table and an expression, calculates the expression once for every row of that table, and then adds up the results. Use SUM when the number you need already exists in a column, and SUMX when each row needs its own calculation first, such as quantity times price.

Both are DAX functions, and both return a single number that respects the filters in the visual.

What are Power BI's SUM and SUMX?

The difference is easiest to see with one dataset used for both. The examples below use a table named score, with one row per customer and columns including Health Score and CSM Sentiment.

Table view of the score table used in the examples.

Sum Function Explained

SUM takes exactly one column and returns the total of its numbers in the current filter context.

SUM ( <column>

To add up the Health Score column:

Total Health Score = SUM ( score[Health Score] )

The Total Health Score measure in the formula bar, shown in a gauge visual.

SUM cannot take an expression. SUM ( score[Health Score] - score[CSM Sentiment] ) is an error, because the argument must be a column reference.

Sumx Function Explained

SUMX is an iterator. It walks through a table one row at a time, evaluates an expression for that row, and adds the results.

SUMX ( <table>, <expression>

To add up the difference between Health Score and CSM Sentiment, called Net Score here:

Net Score = SUMX ( score, score[Health Score] - score[CSM Sentiment] )

The Net Score measure using SUMX, shown in the same gauge visual.

For each row, SUMX subtracts CSM Sentiment from Health Score, then adds up all the row results. With illustrative values, the per-row step looks like this:

SUMX returns 75, the sum of the last column.

When SUM and SUMX give the same answer

For a plus or minus, you do not need SUMX. Adding up row-level differences gives the same total as subtracting one column total from the other:

Net Score SUM = SUM ( score[Health Score] ) - SUM ( score[CSM Sentiment] )

With the four rows above, that is 265 minus 190, which is also 75.

When only SUMX gives the right answer

The results diverge when the row calculation multiplies or divides. Take a Sales table with Quantity and Unit Price:

Revenue is 150. The correct measure is:

Revenue = SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )

Multiplying the two totals instead gives 11 times 105, which is 1,155:

Revenue Wrong = SUM ( Sales[Quantity] ) * SUM ( Sales[Unit Price] )

That is the case SUMX exists for. The alternative is a Revenue column computed per row, which the measures vs calculated columns post compares.

Best Practices While Using The SUMX Function

Avoid Overusing SUMX

If the value already exists in a column, use SUM. SUMX ( Sales, Sales[Amount] ) returns the same result as SUM ( Sales[Amount] ) and is harder to read.

SUMX is not slow in itself. A simple expression over columns of one table, like Quantity times Unit Price, is handled efficiently by the engine. It gets expensive when the expression does heavy work on every row, for example calling a measure, using IF logic, or looking up values in other tables, over millions of rows.

Tips To Optimize Performance While Using SUMX

• Iterate the smallest table that answers the question. SUMX ( VALUES ( Customer[CustomerKey] ), ... ) walks customers, not every sales line.

• Keep the row expression to plain column arithmetic where you can.

• Store repeated results in variables so they are calculated once.

The DAX performance guide shows how to measure whether a SUMX measure is the slow one.

How Does This Help While Using Other DAX Functions?

SUMX is one of a family of iterators. Most common aggregations have an X version that takes a table and an expression:

RANKX works the same way: it iterates a table and ranks each row by an expression. The RANKX guide covers it. Once you know how SUMX iterates, all of these read the same way.

Conclusion

Use SUM when the value is already in one column. Use SUMX when each row needs a calculation that involves multiplication, division or conditional logic before adding up. For plain addition or subtraction of columns, SUM of each column is enough. To see how SUMX combines with filters, read the CALCULATE function guide next.

FAQs

What is DAX?

DAX (Data Analysis Expressions) is the formula language of Power BI, Analysis Services and Power Pivot in Excel. You use it to write measures, calculated columns and calculated tables. SUM, SUMX and CALCULATE are DAX functions. The complete DAX guide introduces the main ones.

Sources

• SUMX function (DAX) - Microsoft Learn

• SUM function (DAX) - Microsoft Learn