Power BI: The Difference Between SUM vs. SUMX


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])

This will create a new measure named Sum Sales that contains the sum of values in the Sales column of 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 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:

How to Calculate Sum in Power BI
How to Use SUM IF in Power BI
How to Calculate a Cumulative Sum in Power BI

Leave a Reply

Your email address will not be published. Required fields are marked *