Power BI DAX — Let’s Understand the Difference Between SUM and SUMX

Welcome to my Power BI blog! I’m passionate about helping people learn and use Power BI to create powerful data visualizations and insights. I’ll be sharing tips, tutorials, and case studies on a variety of topics, such as Data modeling, DAX, Report design etc.

In this blog, I’m going to explain the difference between two important functions in DAX: SUM and SUMX. You might have used them interchangeably, but they are not the same. Let me show you why and how to use them correctly with some real-world examples.

The Basics of SUM and SUMX

SUM Function

SUM is a simple function that takes a column name as an argument and returns the sum of all the values in that column. For example, if you have a Sales table with a column SalesAmount, you can write:

Sales Amount = SUM(Sales[SalesAmount]

This will give you the total amount of sales in your data. Easy, right?

But what if you want to apply some logic or calculation to each row before summing them up?

For example, what if we do not have a SalesAmount column and we need to calculate it? We have OrderQuantity and UnitPrice columns in our table. We can not simply sum both of them up and multiply them to get the Sales Amount as we need to multiply both of these columns for each row and add them up. You would be thinking, I can create a Calculated Column in the table and use this column in SUM Function but here’s a better way to calculate the same without creating a column.

SUMX Function

SUMX is an iterator function that takes a table and an expression as arguments and returns the sum of the expression evaluated for each row of the table. For example, to calculate SalesAmount using OrderQuantity and UnitPrice columns, you can write:

Sales Amount = SUMX(Sales,Sales[OrderQuantity] * Sales[UnitPrice])

This will give you the Sales Amount in your data by multiplying OrderQuantity and UnitPrice for each row and adding them up. Notice how SUMX allows you to use any expression that involves multiple columns of the table, not just a single column.

In addition to SUMX, other iterator functions in DAX can be useful such as AVERAGEX, COUNTX, COUNTBLANKX, COUNTROWSX, DISTINCTCOUNTX, MAXX, and MINX etc.

As you can see, SUM and SUMX are different functions that serve different purposes. You should use SUM when you want to sum up a single column of values that are already in your data model and use SUMX when you want to sum up an expression that involves calculations or logic on each row of a table.

If you enjoyed this blog post and learned something useful and would like to receive notifications about my new blogs, please follow and subscribe to my Medium & NavigateData account. Also, please don’t forget to give me a clap and save it on your reading list. Stay tuned for my next blog posts with tons of other Power BI tips and tricks. Feel free to leave your comments and feedback below. Thank you for visiting!