DAX Power BI: Iterative X-Functions (SUMX, AVERAGEX, COUNTX)
Iterative X-functions are those functions that contain the ending X in their names.
For example:
SUMX — sum
AVERAGEX — average
COUNTX — quantity
MINX — minimum
MAXX — maximum
The X-functions are needed so that we can first calculate some expression over the data, and then, over the result, perform the action for which the X-function was designed.
For example, we can first calculate an expression, and then, based on the calculations of that expression, calculate some aggregation: either a sum, an average, a maximum, or a minimum. X-functions are used when we need to first perform some preliminary action before a simple, ordinary aggregation of a column.
The first parameter is the table, and the second parameter is the expression that we need to execute on each row in the table. After we execute this expression on each row of this table, all results of this expression execution are written into a certain virtual table stored in the computer’s memory.
Initial table
The table connection is available at the link:
Let’s calculate the sum of the cost column:
Now let’s complicate our measure. Let’s try to calculate the amount of profit. Profit is the sales column minus the expenses column. Let’s add another amount to the sales column to the formula and subtract the sum of all costs from this amount.
We calculated the profit using two SUM functions. If we want to calculate the amount of profit using only one function instead of two functions, we can’t do it. We can only insert one column into the SUM function, and we have an expression.
This is the case when, before aggregating a column, you must first calculate an expression and use the results of that expression to perform the aggregation. That is, the aggregation should not be done on the basis of the original column, but on the basis of the executed expression. This is what iterative X-functions are for.
Change the SUM function to the SUMX function. The SUMX function takes a table as its first parameter. It is by this table, on each row of this table, that this expression will be calculated.
Let’s see how the SUMX function worked. First, the SUMX function defined the Orders table. It stored this table in memory. Then SUMX began to calculate an expression for each row in that table. It began to put the results of this expression into a special virtual table. The virtual table is located in the computer’s main memory. This table will be stored in the computer’s main memory while this function is calculated. As soon as this function finishes the calculation, the virtual column will be deleted. Accordingly, SUMX has stored all the results of the expression in this table and only after this expression has been calculated on all the rows of this table, it has performed the aggregation for which it was created. That is, in our case, the SUMX function calculates the sum.
In the second parameter of all iterative X-functions, we can specify as an expression not only some complex expression consisting of calculations of several columns from one table. We can specify just one column from the same table, for example Orders[TotalSum]. That is, don’t specify the sales column minus the cost column, but just specify the cost column.
We have an initial table. In the second parameter, we specify just one column from the same table. Then the amount resulting from the SUMX function will be exactly equal to the amount we can calculate through the SUM function on that column. It is much easier to filter a table rather than a column. So when a situation arises in your practice where you need to calculate a simple aggregation on a column, but the column needs to be filtered beforehand, you will no longer need to use simple aggregation functions, but you will need to use this construction. That is, specify a column in the SUMX function, indicate the table in the first parameter. Just the table you can already filter.
For example, we can filter the table Orders by date
So why would a given expression with an iterative function be absolutely equal to this expression with an ordinary aggregation function?
Let’s see how the SUMX function works in this case. The SUMX function will define a table based on the first parameter. Then the SUMX function will calculate an expression from the second parameter for each row in that table. We have only one column as an expression in the second parameter. Therefore, the virtual table will return the very values that are in this column, which we specified in the second parameter.
For each row, this value will be repeated. It turns out that exactly the same column will be created in the computer’s RAM for which we need to perform the aggregation. The last action of the SUMX function will aggregate this virtual column, that is, it will calculate the sum.
