Calculation Using SUM:
Imagine we want to calculate the total sales but we only have Quantity and unit price what we need to do in pseudo-code is Sum( Quantity*Price )
This is what this would look like in DAX
Total Sales = SUM(Sales[Quantity] * Sales[Unit Price])
However, this will not work correctly because SUM does not perform row-level calculations. The expression Sales[Quantity] * Sales[Unit Price] is treated as a column-wide reference, and SUM will try to sum up the entire product of the two columns.
Calculation Using SUMX:
To achieve the correct result, you would need to calculate the row-level calculation first (i.e., Quantity*Price) and then sum it. Here’s how you would use SUMX to achieve this:
Total Sales = SUMX(Sales, Sales[Quantity] * Sales[Unit Price])
Explanation of SUMX:
- SUMX iterates over each row in the Sales table.
- For each row, it calculates (Quantity) * (Unit Price).
- Then, it sums all the calculated values (total sales) across the rows.
Example
| Quantity | Unit Price | Quantity * Unit Price |
|---|---|---|
| 5 | 10 | 50 |
| 3 | 15 | 45 |
| 2 | 20 | 40 |
In this case, the Total Sales calculated by SUMX would be:
- (5 * 10) + (3 * 15) + (2 * 20) = 50 + 45 + 40 = 135.