In DAX (Data Analysis Expressions) for Power BI, both SUM and SUMX are functions used to perform aggregation, but they operate differently and are used in different scenarios.
SUM Function
The SUM function is straightforward and is used to add up all the values in a single column.
Syntax
DAX
SUM(<column>)
<column>: The column containing the values you want to sum.
Example
Consider a table Sales with a column TotalSales. To create a measure that sums all the values in the TotalSales column, you would write:
DAX
TotalSalesSum = SUM(Sales[TotalSales])
Here, SUM simply adds up all the values in the TotalSales column.
SUMX Function
The SUMX function is more versatile and is used to perform row-by-row calculations before summing up the results. It iterates over a table, evaluates an expression for each row, and then sums the results of these evaluations.
Syntax
DAX
SUMX(<table>, <expression>)
<table>: The table over which to iterate.<expression>: The expression to evaluate for each row.
Example
If you have a table Sales with columns Quantity and UnitPrice, and you want to calculate the total revenue by multiplying Quantity by UnitPrice for each row and then summing the results, you would use:
DAX
TotalRevenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
Here, SUMX iterates over each row in the Sales table, calculates Sales[Quantity] * Sales[UnitPrice] for each row, and then sums these calculated values.
Summary
- SUM: Adds up all the values in a single column. Use it when you need a simple aggregation of values in one column.
- Example:
SUM(Sales[TotalSales])
- Example:
- SUMX: Performs row-by-row calculations using an expression and then sums the results. Use it for more complex scenarios where you need to compute values for each row before aggregating.
- Example:
SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
- Example:
Both functions are essential in DAX, with SUM being used for straightforward column summation and SUMX for more complex, row-wise calculations.
