Difference Between SUM() and SUMX

Question

What is the difference Between SUM() and SUMX()?

Daniel Wright · Answer

Absolutely! The main difference between the SUM() and SUMX() functions lies in how they handle calculations in DAX, which is a formula language utilized in Power BI, Excel Power Pivot, and Analysis Services Tabular models. - SUM(): The SUM() function in DAX is a straightforward aggregation function that calculates the sum of a column in a table or the expression provided as an argument. It is ideal for simple calculations where you need to sum up values in a column. Example: TotalSales = SUM(Sales[Amount]) In this example, the SUM() function calculates the total sales amount from the 'Amount' column in the 'Sales' table. - SUMX(): On the other hand, SUMX() is an iterator function that allows you to perform calculations on a table, evaluating an expression for each row and then summing up those individual results. This is particularly useful when you need to iterate over a table and apply a more complex calculation or logic row by row. Example: TotalSalesX = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) In this case, the SUMX() function iterates over each row in the 'Sales' table, multiplying the 'Quantity' by the 'UnitPrice' for each row, and then sums up all those individual results to give the total sales amount. In summary, while SUM() is suited for straightforward column-based summation, SUMX() offers the flexibility to perform more complex calculations and iterate over tables row by row to arrive at a cumulative result. Both functions have their specific use cases based on the complexity of the calculation you need to carry out. I hope this helps clarify the difference between SUM() and SUMX() for you! Let me know if you need further examples or have any other questions on this topic.