Data Bear – Power BI Training and Consulting

Call us Today! (020) 8720 6880 | info@databear.com

# 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.