Table of Contents
SUM is a function in Power BI that calculates the sum of a column or measure in a table or visual. It returns a single value. On the other hand, SUMX is an iterator function that performs a calculation for each row in a table and then sums up the results. It is useful when working with multiple tables or when a calculation needs to be performed for each row in a table. It returns a table with a single row and column, which can then be used in further calculations or as a measure in a visual.
You can use the SUM and SUMX functions in DAX within Power BI to calculate the sum of values, but they work in slightly different ways:
The SUM function returns the sum of values in a single column.
For example, you can use the following syntax to calculate the sum of the Points column in a table named my_data:
Sum Sales = SUM('my_data'[Sales])
The SUMX function takes multiples columns as input and returns the sum of an expression that is evaluated for each row in a table.
For example, you can use the following syntax to calculate the sum of the difference between the Sales and Returns columns in a table:
Sum Sales = SUMX('my_data', 'my_data'[Sales] - 'my_data'[Returns])
Or you could use the following syntax to calculate the sum of values in the Sales column of a table, filtered based on where the corresponding value in the Store column is equal to A:
Sum Sales = SUMX(FILTER('my_data', 'my_data'[Store]="A"),[Sales])
The following examples show how to use both the SUM and SUMX functions in practice with the following table named my_data in Power BI:
Example 1: Use SUM to Calculate Sum of Values in Column
Suppose we would like to calculate the sum of values in the Sales column.
To do so, click the Table tools tab along the top ribbon, then click the New measure icon:
Then type in the following formula into the formula bar:
Sum Sales = SUM('my_data'[Sales])
If we’d like, we can display this value by inserting a Card visualization into our report:
We can see that the sum of all values in the Sales column is 107.
Example 2: Use SUMX to Calculate Sum of Differences Between Two Columns
Suppose we would like to calculate the sum of the differences between the Sales and Returns columns.
To do so, click the Table tools tab along the top ribbon, then click the New measure icon:
Then type in the following formula into the formula bar:
Sum Sales = SUMX('my_data', 'my_data'[Sales] - 'my_data'[Returns])
This will create a new measure named Sum Sales that contains the sum of the differences between the Sales and Returns columns in the table:
If we’d like, we can display this value by inserting a Card visualization into our report:
We can see that the sum of the differences between the Sales and Returns columns in the table is 85.
Example 3: Use SUMX to Calculate Sum with Filter
Suppose we would like to calculate the sum of the values in the Sales column, filtered based on the rows where the Store column is equal to A.
To do so, click the Table tools tab along the top ribbon, then click the New measure icon:
Then type in the following formula into the formula bar:
Sum Sales = SUMX(FILTER('my_data', 'my_data'[Store]="A"),[Sales])
This will create a new measure named Sum Sales that contains the sum of the values in the Sales column, filtered based on the rows where the Store column is equal to A:
If we’d like, we can display this value by inserting a Card visualization into our report:
We can see that the sum of the values in the Sales column for the rows where the Store column is equal to A is 64.
Summary: The Difference Between SUM vs. SUMX in Power BI
In summary:
- The SUM function is used to calculate the sum of values in one column.
- The SUMX function is used to calculate the sum of an expression that can include multiple columns in a table and can be used with the FILTER function.
Feel free to use either the SUM or SUMX function depending on the type of calculation that you would like to perform.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: