The SUMX
function in DAX is an iterator function, which means it calculates each row individually before summing up the results. Unlike the regular SUM
function, which simply adds up values in a single column, SUMX
allows you to perform more complex calculations on a row-by-row basis, making it extremely versatile.
Syntax:
- Table: The table you want to iterate over (e.g.,
Sales
). - Expression: The calculation to be performed on each row before summing the results (e.g.,
Sales[Quantity] * Sales[Price]
).
Example 1: Calculate Total Revenue from Quantity and Price
Let’s say you have a Sales
table with Quantity
and UnitPrice
columns, and you want to calculate the total revenue. Using SUMX
, you can multiply Quantity
by UnitPrice
for each row and then sum up the results:
Here’s what happens:
SUMX
goes through each row in theSales
table.- For each row, it calculates
Sales[Quantity] * Sales[UnitPrice]
. - Finally, it sums up all these row-level results to return the total revenue.
Example 2: Calculate Discounted Total Sales
Imagine you have a Discount
column in the Sales
table, where each discount is a percentage (e.g., 0.1 for 10%). To calculate the total sales amount after applying these discounts on a row-by-row basis, you can use SUMX
:
This formula first calculates the discounted amount for each sale, then sums up the results. SUMX
is essential here because it handles row-by-row calculations based on individual discounts.
Why and When to Use SUMX
?
- Row-Level Calculations:
SUMX
is perfect for situations where you need to perform calculations on each row before aggregating, like multiplying quantities and prices or applying specific row-based discounts. - Flexibility Over
SUM
: WhileSUM
is straightforward, it can only add values in one column.SUMX
is more versatile, as it allows you to define a custom expression for each row. - Complex Aggregations: Use
SUMX
for more advanced calculations, such as conditional or multi-column aggregations.
Key Tip: SUMX
can be slower on large datasets since it iterates row by row, so use it when you need that row-level control. For simple sums, SUM
is faster and more efficient.