The total value that you see in a table is not actually SUM of all values in that column, it is, in fact, the calculation, when there is no filter. This, however, might not be the calculation that we want sometimes. we might want this value to be the sum of values of that column in the table visual. In this simple example, I’ll show you an easy method to calculate the total using Iterator functions in DAX. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.
Defining the Problem
I have written an article a long time ago, about how to use DAX calculations to calculate the lost vs new customers using Power BI. That article showed a result like below:
The calculation works perfectly fine as you can see in the row level. However, it doesn’t work at the total level. The count of the total is always zero in the above expression. The sample above works with the calculations below:
Sales = SUM(FactInternetSales[SalesAmount])
Sales last period measure:
(The value of this measure changes with the selection of period slicer – period slicer comes from a What If parameter)
Sales Last Period = CALCULATE( [Sales], DATESINPERIOD( FactInternetSales[OrderDate].[Date], LASTDATE(FactInternetSales[OrderDate].[Date]), -1*[Period Value], MONTH) )
New Customer measure:
New Customers = IF([Sales Last Period]=[Sales],1,0)
Lost Customer measure:
Lost Customers = IF([Sales Last Period],0,1)
To learn more about the details of the calculations above, read my article here.
Iterator Functions in DAX
Iterators in DAX are functions that iterate through all rows of the given table, apply the expression, and then aggregate the result. One of the known functions in this category is SUMX. because an iterator function goes through every row and apply the transformation, then the result is materialized (temporary) before aggregating it, and that causes the totals to be actual sum (or other aggregations) of values in a column.
So using SUMX, I can add a calculation like this:
New Customers Total Count = SUMX( DimCustomer, [New Customers] )
All you see in the above expression is that I am saying calculate the [New Customers] measure value once for every row in the customer table. This result will be stored in the temporary memory, and then at the end summarized (because we are using SUMX).
The same method can be used for Lost customers too:
Lost Customers Total Count = SUMX( DimCustomer, [Lost Customers] )
Testing the result
If you add the two measures with the above calculation in the visuals, you can see that they show the aggregated total values:
because in this report I am also using what-if parameters, so it changes with the change of the period value;
SUMX and Iterators are only one of the ways that can help you to create the totals, but it is not the only way. Sometimes, you might find the performance of iterator functions slower than other methods.