COMMON POWER BI DAX MEASURES

POWER BI DAX FUNCTIONS: SUM, SUMX AND CALCULATE

In this blog, we’ll be looking at some common Power BI DAX functions. We will be looking at the very simple yet very useful SUM, SUMX and CALCULATE. 

Let’s jump right in and see what useful functions Power BI DAX has in store for us and how to use these functions. 

If you prefer to watch the YouTube video, rather than reading the blog, please see the video below: 

DAX INTRODUCTION 

DAX (Data Analysis Expression) consists of a set of functions, operators and constants. When these elements are combined in a formula, or expression, it will calculate and return one or more values. Simply put, DAX is a powerful way to generate new information from already existing data in your data model. Just how useful this is, will become apparent in our upcoming blog posts.   

DAX SYNTAX

The DAX syntax is made up of different elements and when combined results in the following formula. 

1. Name of the measure, Total Sales 
2. Equal sign operator (=) shows the start of the formula and will return a result when calculated 
3. The DAX function SUM adds all the numbers in the Sales[UnitPrice] column. 
4. () surrounds the expression with one or more arguments, the argument provides the value to a function 
5. This is the table that is referenced, Sales.  
6. This indicates the reference column [UnitPrice], the column to aggregate to a SUM, in the Sales table.

Now that we understand how the elements of DAX work, let’s dive straight into a few examples of SUM, SUMX and CALCULATE.  

SUM

Let’s start with SUM. Most of you know what SUM is, if you’ve ever used Excel or some other software programs, SUM comes quite naturally. We understand SUM adds up all the values in a column, and because of this the SUM syntax requires a column as input.  

SUM(<column>) 

In order to see how this function operates, let’s create a new measure. Let’s say that we want to calculate the value of all our individual items sold. We will call our measure SUM and we’ll SUM the column ‘Total Sales’ from our ‘Sales’ table. SUM is going to look at the ‘Total Sales’ column in the ‘Sales’ table and sum all the values together. 

SUM = SUM(Sales[Total Sales]) 

Let’s use a matrix visual to display the measure ‘SUM’ by ‘ITEMNAME’. To do this, we add ‘ITEMNAME’ as the row value and ‘SUM’ as our values in Power BI. Here we can see that SUM does exactly what we expect it to do. It will sum the values for each ‘ITEMNAME’ in the table together and return us a Total at the bottom. This total is the Total value for ‘SUM’ of all the ‘ITEMNAMES’.  

SUMX

Let’s create a new measure now to see how the function SUMX operates. The first notable difference for SUMX is that you need to provide the table name and an expression, so SUMX returns the sum of an expression evaluated for each row in the table. The term <table> contains the rows for which the expression will be evaluated, and the term <expression> evaluate each row of the table 

SUMX(<table>, <expression>) 

In other words, let’s say we want to determine the Total sales for each unit, we will have to choose the ‘Sales’ table, and, in the ‘Sales’ table, we have two values, quantity of units sold ‘QTYNET’ and the unit price ‘Unit Price’ that must be multiplied to determine the Total sales for each unit. 

SUMX = SUMX(Sales,Sales[QTYNET]*Sales[Unit Price]) 

Let’s add the measure to our canvas and immediately we see it does the same thing, it sums the Total sales for each item.  

So, the difference here is that SUMX goes to each row in the table and it says multiply the quantity by the unit price and then give us the Total sales amount and does that iteratively for each row in the table and once it reaches the bottom it does a sum and adds up all those values together. SUM on the other hand basically multiplies the two values together. So, a good comparative example is where SUM would be ten times ten and SUMX would be 10 + 10 + 10 + 10, 10 times. Ultimately, they get to the same amount but that’s the difference in how these two functions operate. Because of that SUMX is not as efficient as SUM, but depending on your situation, it may give you the ability to do a little more. 

CALCULATE

If you are new to DAX, the SUMX and SUM measures that we just created can be used in other DAX calculations.  What I mean is that you can use one measure within another measure, and we will do that now when we get to the CALCULATE function. The CALCULATE function requires an <expression> and a comma separated list of Boolean (true or false statements) expressions, defining one or more <filters>, which is optional to fill in.

CALCULATE gives us the syntax below:

CALCULATE(<expression>,<filter1>,<filter2>…) 

In this expression you can do one of two things when you want to do a SUM of the Sales and then apply a filter. You will be able to use either an existing created measure such as the SUM or SUMX that we have just created, or alternatively you will need to apply the SUM or SUMX function in the expression referencing the table ‘Sales’ and column ‘Total Sales’ as below.    

CALCULATE = CALCULATE(SUM(Sales[Total Sales]) 

You could do as in the above formula, but best practice for DAX would be to build on top of the previous measures that you have created. In this case, I am going to use the SUM measure that I have already obtained and apply a filter to that measure. So, for example, let’s say that we want a table like the one that we have created, but we don’t want to see the values for all items. We want to filter this table by a customer name. In order to filter by the customer name ‘ACME Plumbing’, we take the column ‘CUSTOMERNAME’ from the ‘Customers’ table where it equals ‘ACME Plumbing’.

Great, let’s see the formula for this measure below:

ACME Plumbing = CALCULATE([SUM],Customers[CUSTOMERNAME]=”ACME Plumbing”) 

Let’s add this measure to our canvas. We see a much shorter list of item names because of the filter we’ve applied to this calculation. This list only sums the values of items where the filter for ‘CUSTOMERNAME’ is ‘ACME Plumbing’.

Another example would be to say  that we want to calculate the Total Sales for two companies called ‘ACME Plumbing’ and ‘Custom Comfort’. This can be done in the same way as we did previously when calculating the Total Sales for ‘ACME Plumbing’. Let’s do this same measure now for ‘Custom Comfort’.

Custom Comfort = CALCULATE([SUM],Customers[CUSTOMERNAME]=”Custom Comfort”) 

Let’s add the measure to our canvas.  

Now, we can add ‘ACME Plumbing’ and ‘Custom Comfort’ to one table.

Wow, we can see what’s happening here. This table shows the combined sales and items sold for both companies ‘ACME Plumbing’ and ‘Custom Comfort’ above.

Congratulations! This is the first step in the process of becoming a master in DAX. It is a journey not a destination, so stick with us and we will continue showing you how these calculations work.

For more great posts go here.  Attend our training to learn even more.

That’s it for this post, but we will see you next time.