The role of both the SUM and SUMX functions in DAX are to add numerical data together. Yet both these functions are rather different.
Lets talk about the SUM function first. The SUM function is simple. It takes a column of data and adds the values to give a total. The syntax is SUM(). It works like the sum function in Excel however Excel works on cell references or cell ranges and DAX works in columns.
But if you want to filter the data which you are adding, then you need to use the SUMX function. The Syntax is SUMX(Table , Expression).
It takes a table that contains the rows for which an expression (or the formula) is to be evaluated. It then takes the expression to be calculated (or evaluated) on each row of the selected table. Comparing DAX SUMX to Excel, you could say it works a bit like SUMProduct and complex Array formula.
In this video we are going to look at both SUM and SUMX in DAX. We will use Power Pivot in our example, but this works just the same in Microsoft Power BI. You might already know these functions, but just know that they work. If you really want to master DAX and understand how it works, I would suggest that you have a look at the video anyway as you may learn how these functions work.
Script - SUM and SUMX Functions
These functions (SUM and SUMX and all DAX Functions) work in PowerPivot and they also work in Power BI. As usual we're going to look at this example using PowerPivot. This is because when you use PowerPivot it's much easier to see the end result of your calculation (or your measure) then when you're using Power BI.
So what we have is, we have a sales table. We've got the date, invoice number, customer ID, product ID. We have quantity and we have sales.
We also have a product table and it contains a product ID, a category, name, a colour, supplier ID, the cost price and also a sales price.
What we want to do is, we want to be able to calculate the total of the sales (or the sum of the sales). We're going to look at doing this in a number of different ways.
First we're going to look at the normal SUM function. Then we're going to look the SUMX function. After this we're going to talk about the differences between the two functions and when you should use each one of them.
One of the first thing that we notice is that we already have a sales figure in our table of data that we've loaded into PowerPivot. (Now don't forget is the same as if you were using Power BI.) And if we wanted to get the total of all of these sales we could add in a measure. In Excel 2013 in PowerPivot, the measures can be done in this calculation part of the screen. You can show and you can hide the calculation area using the calculation area toolbox.
Let's go ahead and calculate our sales. Selecting the cell down here in our in our calculation field, if we put in =SUM and we'll see in our formal bar we can select SUM like with Excel. Now what the function is looking for next is a column name. If you start with the table name, and we know our table name is ‘Sales’, and we can see the "T" in there to see that it a sales table. But we want to take our sales column in our sales table and then we can press enter. Now what we do is we just make this a little bit bigger so we can actually see the value that coming into our calculated field or into our measure. We can rename this 'Total Sales'.
So what we've done there is we have a column that is already in the table and we just summed this column. But what if we didn't have the sales value in this table here? What if we only have the quantity and we wanted to get the total sales?
What we do have in our product table is a sales price. Because these are joined together by relationship we can multiply in our sales table our quantity by our sales price. This will give us actual sales value.
So let's have a look at adding this into a calculated column rather than into a measure. To do this we need to use the RELATED function. Let’s quickly have a look at this - what we want to do is we want to start with equals and we're in the column so we're going to take our quantity and we're going to multiply our quantity. And what are we going to multiply it by? Well we need to look at the RELATED function and we're going to are related table of products and in our products we are going to get our sales price.
So basically what this formula says is; take the quantity and then go over to the table products’, find the related sales price for this particular product and then multiply it by the quantity and we can hit enter. And what this will do is this will now actually calculate the Sales for us. I'm going to right-click and I rename this column and I'm going to call this 'Calculated sales' (because we already have in a column called sales, so that is our calculated sales.)
Now if we want to get our total sales, we would say equal to sum' and we will go to our sales table and we would take are calculated sales and we will be able to sum them to get the total value for our sales if we actually didn't have sales in this table.
So that's how we would reach into another table we would carry out the calculation and then we will get a total Sales. But this way is quite long and what we will find is it are also very resource-intensive or if you are using a PowerPivot, because each one of these calculations is actually stored in the model because it is a calculated column and it's another measure, whereas this isn't measure and this is a measure so they're only calculated when they're actually needed.
But this particular column here is calculated by way of a calculated column so what DAX does is it goes to the first row and in the first row goes to the quantity it hopped over to the products table it looks for the sales price of the product table and multiplies it. It then goes down to the next row and does the exact same thing for the next row. So this is at roll level and this is an important concept of DAX to actually understand as you progress through your DAX studies and get more involved in DAX.
What we're going to look at now is another solution for this calculated sales. So let's assume we didn't have again we didn't have this sales column in our table of data and we didn't want to have a new calculated column. All we wanted to do is actually have a measure. Well what we can do now is we can use the sum X function.
The SUMX function will allow you to sum things across different tables. Let me show you what I mean. What we do is, we will calculate a new measure down here, and then your measure is going to be SUMX. And SUMX says it returns the SUM of an expression evaluated for each row in a table. So its a table function and the first thing it's looking for is the table. So what table is it that this calculation is going to take place in?
Well the table is going to be our sales table. Now we're saying OK, well what expression do you want to SUM in the sales table? Basically your expression is your formula. So our formula is going to be our quantity. We're going to take the sales table and we're going to take quantity. And as before we're going to multiply this by the Related function and we're going to go to our products table and in our product table we are going to go to our sales price. I'm going to close the bracket.
Now what is this formula saying? Well the formula is saying go to the sales table get the sales quantity multiplied the sales quantity by the related product price. So it will go one by one, row by row, down to the sales table taking the sales quantity multiplying it by the related price in the products table. And then when it's done all that what its then going to do is, it’s going to go to this outer formula SUMX and it's going to sum all of these together.
Now I just need to close the second bracket because I only had the first bracket closed. I need to close the bracket on the SUM X formula. We can also change the name of our calculated field (which is also a measure). We can change the name of this to 'Total Sales SUM X'. Just because we already have total sales and we have and we have calculated in . We can see now that our total sales produced in this manner using the SUM X function gives us the same result as the sum function that we used by just adding up the sales column. And also then by using the related function to look up the sales price and multiply it by the quantity. I'm doing that as a calculated column and then creating a measure to total this calculated column.
SUMX in effect means that you don't really need to have loaded Sales into your model in the first place and you don't need to always create calculated columns. This measure that we have created it can be sliced and it can be diced.
You see the SUMX function it's an iterator and this is the term that you will become more familiar with. And what that means is that it defines the ROW context for the DAX formula so that means it tells DAX on which rows that the calculations should be carried out. And when you add this to a visualization or to a pivot table and then you add filters on it, the row context has already been defined by using this sum X function. So the row context and the filter context are things to take into consideration when you're creating DAX measures. So that's the sum function and the SUMX function.