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(<column>). 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.
In return for this DAX for Power BI and Power Pivot tip – DAX SUM and SUMX Functions, I ask that you share this post or the video with your friends and colleagues
Sign up for my newsletter – Don’t worry, I wont spam – Just useful Excel and Power BI tips and tricks to your inbox SIGN UP NOW
FREE Course ‘Power BI – The Ultimate Orientation’ available here on the website or you can download the app and keep it close for reference.