When people first start with Power BI, they are often confused about the two functions in DAX - SUM and SUMX. Both functions are doing the aggregation and it may be hard to see the difference between the two.
In this article, we take a look at the SUMX function and learn the difference between the two key aggregation functions in DAX.
First, let's start with SUM. This is a basic sum function, similar to one in Excel. For example, if you would like to calculate the sum of all sales transactions in a store within a day, you will use the SUM function.
SUMX Function
SUMX is an iteration function in DAX that does a row-by-row calculation based on a given expression or equation. The function considers each row in a table and if it fits the set parameter the function applies the calculation. In other words, SUMX is an iterator function, because it iterates through all the rows in a table and calculates the expression for every single row.
The function SUMX has two parameters – a table and expression. The table parameter references a named table in a data model, while the expression completes a specified calculation row by row.
Let's take a look at the example from the video:
Sales = SUMX (SalesTable, SalesTable[Price] * SalesTable [Quantity] )
The easy way to understand this function is to think of it as a two-step process. The first step multiplies each row of the column [Price] by a corresponding row in the column [Quantity]. Then it temporarily stores the preliminary results in the backend memory.
As the second step, the function sums up the preliminary results, giving us a total.
Watch our short tutorial with a clear example to understand the power of the SUMX function.