The role of both DAX SUM and SUMX functions 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.
Sign up for my newsletter – Don’t worry, I wont spam – Just useful Excel and Power BI tips and tricks to your inbox
FREE Course ‘Power BI – The Ultimate Orientation’ available here on the website
Master DAX Fundamentals : Power BI & Power Pivot and Analysis Services
Sign up to the newsletter to get notified when it is live
DAX SUM and SUMX functions
We are going to start writing some DAX code straight away and look at the SUM and SUMX expressions for Power Pivot, Power BI and SSAS.
These functions (SUM and SUMX and all DAX Functions) work in Power Pivot and they also work in Power BI and Analysis Services.
As usual we're going to look at this example using Power Pivot. This is because when you use Power Pivot 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 a sales table. This table includes columns for the date, invoice number, customer ID, product ID, quantity and we have sales.
We also have a product table and it contains a column for product ID, a category, name, a color, 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.
SUM in DAX
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 Power Pivot. (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. Now in Excel 2013 in Power Pivot, the measures can be done in this calculation part of the screen you can show and you can hide the calculation area using this 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, so because these are joined together by relationship we can multiply in our sales table our quantity multiplied by our sales price to 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. The full function we need to use is
=[qty] * RELATED(Products[Sales Price]
Let’s quickly have a look at this : We want to start with equals. Because we're in the column we're going to take our quantity and we're going to multiply our quantity. But what are we going to multiply it by?
Well we need to look at the RELATED function and we're going to our related table of products. 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. 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'.
Now if we want to get our total sales we now need to SUM our new calculated column. In the measures area enter the following expression
This measure will now tot all of the values in our calculated column.
So that's how we would reach into another table then carry out the calculation and finally get a total Sales value.
But this way is quite long and what we will find is it are also very resource-intensive.
Calculations carried out in a calculated column are actually stored in the model. Whereas measures are only calculated when they're actually needed.
The column [Cal Sales] is calculated by way of a calculated column. What DAX does is it goes to the first row and in the first row goes to the quantity and takes that value. Then it hops over to the products table it looks for the sales price for that product and multiplies it by the quantity. It then goes down to the next row and does the exact same thing for that row, and so on. This is at roll level and this is an important concept of DAX to actually understand.
SUMX IN DAX
What we're going to look at now is another solution to calculate sales.
Let's assume we didn't have the 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 SUMX function.
The SUMX function will allow you to sum things across different tables. It would remind me of SUMPRODUCT in Excel. Let me show you what I mean.
We will calculate a new measure, and the measure is going to use SUMX.
The full measure is
=SUMX(Sales, Sales[Qty]* RELATED(Products[sales price]))
SUMX says it ‘returns the SUM of an expression evaluated for each row in a table’. So it’s a table function and the first thing it is 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. Next 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. So 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 then, multiply 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.
We can now change the name of our calculated field (which is also a measure) and we can change the name of this to 'Total Sales SUMX (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 SUMX 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. We created a calculated column to get the sales for each row and then creating a measure to total this calculated column. So 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. 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 SUMX function. The row context and the filter context are things to take into consideration when you're creating DAX measures.