When working with Power BI, knowing the difference between SUM and SUMX in Power BI is the key.
You often need to aggregate data using measures. Two of the most commonly used functions for summing values are SUM and SUMX. While they might seem similar, they behave differently and serve distinct purposes. In this blog, we will explain the difference between SUM and SUMX in Power BI with examples, so you can understand when to use each function effectively.
SUM is an aggregation function that calculates the total of a column. It simply adds up all the values in a specified column, returning a single scalar value.
Syntax of SUM:
Where, Column is the column that contains the numbers to sum.
Consider a Sales Table with the following data:
| Order ID | Product | Sales Amount |
|---|---|---|
| 1 | A | 500 |
| 2 | B | 700 |
| 3 | C | 300 |
| 4 | A | 600 |
If we create a measure using SUM:
Total Sales = SUM(Sales[Sales Amount])This measure will return 1700 because it simply adds all values in the Sales Amount column (500 + 300 + 200 + 700 = 1700).
SUMX is an iterator function, meaning it evaluates row-by-row before summing the values. Instead of summing a single column, SUMX allows you to apply calculations to each row before aggregating the results.
Syntax of SUMX:
SUMX(<table>, <expression>)Where, table is the table containing the rows for which the expression will
be evaluated. and.,
expression is the expression to be evaluated for each row of the table.
Example of SUMX:
Consider a Sales Table with the following data:
| Order ID | Product | Sales Amount | Quantity |
|---|---|---|---|
| 1 | Laptop | 500 | 2 |
| 2 | Phone | 300 | 1 |
| 3 | Tablet | 200 | 3 |
| 4 | Laptop | 700 | 1 |
Now, if we want to calculate Total Revenue (Sales Amount * Quantity), using SUM won’t work since SUM only adds a column. Instead, we use SUMX:
Total Revenue = SUMX(Sales, Sales[Sales Amount] * Sales[Quantity])Step-by-Step Calculation:
Final result = 1000 + 300 + 600 + 700 = 2600
We cover all these topics in our Power BI training course. Check out the detailed course content here!
| Feature | SUM | SUMX |
|---|---|---|
| Type of function | Aggregation | Iterative (Row-by-Row) |
| Usage | Sums up a column | Performs calculations first, then sums the results |
| Input | Single column | Table and expression |
| Example | SUM(Sales[Sales Amount]) | SUMX(Sales, Sales[Sales Amount] * Sales[Quantity]) |
| When to Use | When you need a direct sum of a column | When calculations must be performed on each row before summing |
Use SUM when:
Use SUMX when:
Rule of Thumb: Use SUM for direct column aggregation and SUMX when calculations must be done per row first.
Understanding the difference between SUM and SUMX in Power BI is crucial for writing efficient DAX calculations.
Use SUM for straightforward column aggregations and SUMX when calculations must be applied row by row.
Mastering these functions will help you build better reports and optimize performance in Power BI.Want to learn more about Power BI functions? Check out our blog section for more details.
Also, check out the official Microsoft DAX Documentation Page for more details.