Calculate Totals in Power BI: Using Iterators in DAX

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 measure:

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;

Summary

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.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.