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 the`Sales`

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**: While`SUM`

`SUM`

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.