The main difference between SUM and SUMX in DAX is that SUM simply adds up all the values in a single column, whereas SUMX is an iterator function — it goes row by row through a table, evaluates an expression for each row, and then sums the results.
To put it simply, SUM is straightforward and works only on one numeric column. For example:Total Sales = SUM(Sales[Amount])
This just adds up all the values in the Amount column.
But if I need to calculate something that involves a calculation per row, I’ll use SUMX. For instance:Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Price])
Here, SUMX first multiplies Quantity and Price for every row in the Sales table, and then sums up all those calculated values. This can’t be done with plain SUM because SUM can’t handle expressions — it only works directly on a column.
In one of my Power BI projects, I had to calculate “Total Revenue after Discount.” Since discounts were stored in a separate column, I used SUMX(Sales, (Sales[Quantity] * Sales[Price]) - Sales[Discount]) to ensure the discount was applied per row before aggregation. Using SUM in that case would have given an incorrect result because it would sum columns independently before doing subtraction.
A challenge I faced with SUMX was performance — in very large datasets, since it iterates row by row, it can be slower than SUM. To address this, I try to push simple aggregations to Power Query or use SUM whenever the calculation doesn’t require row-level iteration.
So, in short — use SUM for direct column totals when you don’t need per-row logic, and use SUMX when you need to evaluate an expression for each row before summing.