Calculate Totals in Power BI: Using Iterators in DAX

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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

(The value of this measure changes with the selection of period slicer – period slicer comes from a What If parameter)

New Customer measure:

Lost Customer measure:

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:

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:

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply

Your email address will not be published. Required fields are marked *