SUM vs SUMX in Power BI: What’s the Difference and When to Use Them?

In this blog post, I will explain what SUM and SUMX are, how they work, and when to use them. I will also show you some examples and tips to help you understand these functions.

What is SUM?

SUM is a simple aggregation function that takes a column of data and adds the values to give a total. It works like the sum function in Excel, but Excel works on cell references or cell ranges and DAX works on columns. 

= SUM(ColumnName)

For better understanding refer example no. 1.

What is SUMX?

SUMX is a more complex function that takes a table and an expression as inputs and returns the sum of the expression evaluated for each row of the table. It allows you to perform calculations or logic on each row of a table before summing up the results. 

= SUMX(Table, Expression)

For better understanding refer example no. 2 and 3.

 
Differences between SUM and SUMX

Features	SUM	SUMX
Input	Accepts only a single column.	Takes a table and an expression.
Syntax	= SUM(ColumnName)	= SUMX(Table, Expression)
Speed and Simplicity	Quick and straightforward.	More powerful and flexible, but can be slower.
Calculation Logic	Does not perform calculations or logic on column values.	Applies calculations or logic to each row before summing up.
Filter Context Impact	Not influenced by filters.	Takes filter context into account; results may vary based on applied filters.
Applicability	Ideal for basic numeric aggregations without row-level considerations.	Suited for scenarios demanding row-level calculations and flexibility.
Performance	Generally, faster with simple aggregations.	May be slower due to row context and complex calculations.
Context Independence	Operates independently of row context.	Introduces row context, influencing calculations for each row.
Flexibility	Limited flexibility in handling complex scenarios.	Adaptable and powerful, suitable for intricate calculations.

Choosing the Right Tool: When to Use SUM and SUMX?

Use SUM when:

• You want to sum up values from a single column.
• You're working with straightforward numeric columns.

Use SUMX when:

• You need to apply calculations or logic to each row before summing up.
• You're working with related tables and want to consider filter contexts.­ 
• You want to apply conditional logic to values before summing them up.­ 
• You want to apply a different filter context before summing up values.

Examples and Tips

To illustrate the difference and usage of SUM and SUMX, let’s look at some examples using the following sample data. The data has 4 columns: Product, Category, SalesAmount and ProductCost, let’s see how we can use SUM and SUMX to calculate different metrics from this table. 

(Kindly download the sample file and import it into Power BI for practice.)

Example 1: Total Sales Amount

To calculate the total sales amount, we can use either SUM or SUMX, as they will return the same result. For this example, we can use the following formula:

TotalSalesAmount = SUM(Sales[SalesAmount])

This formula will return the sum of all the values in SalesAmount column, regardless of any filters or slicers applied to the table. 

Example 2.1: Total Margin
To calculate the total margin, we have to use SUMX, as we need to subtract the product cost from the sales amount for each row. 

For example, we can use the following formula:

TotalMargin = SUMX(Sales, Sales[SalesAmount] - Sales[ProductCost])

Example 3: Total Sales of Bikes

To calculate the total sales of bikes, we have to use SUMX, as we need to apply a conditional logic to the sales amount column. 

For example, we can use the following formula:

TotalSalesOfBikes = SUMX(Sales, IF(Sales[Category] = "Bike", Sales[SalesAmount], 0))
 

Conclusion
In conclusion, SUM and SUMX are essential functions in Power BI or Excel for summing values. Choose SUM for summing a single column from the data model and opt for SUMX when dealing with expressions involving calculations or logic on table rows.

I hope this blog post helped you understand the difference and usage of SUM and SUMX. If you have any questions or feedback, please leave a comment below. I would love to hear from you. Thank you for reading!