I’m going to show you today how to use iterating functions when you’re trying to fix Total issues in Power BI. You may watch the full video of this tutorial at the bottom of this blog.
When I go through the questions posted on the Enterprise DNA support forum for members, I see a lot of people having issues with the Totals they get on their report regardless if they’re using advanced logic or not.
Here, I’ll show you why that happens and how you can get around those issues.
Computing Min Revenue Totals
Let’s use a simple example on computing Min Revenue Totals.
The data below shows that I have my Total Revenue as well as the Total Revenue Last Year.
Getting last year’s revenue is simple. I just used the DATEADD function.
The Total Revenue shows data for the year 2018. Then you’ll see that we subtracted one year under the DATEADD function, which gives us the 2017 data.
You’ll see that we also have another column after Revenue Last Year, which is the Min Revenue Year. Basically, this column should show the lowest numbers out of both 2018 and 2017.
Alternatively, you can also click on the legend and choose a different year. So you can use the revenue for 2016 and 2017, for example.
But for this example, let’s stick to the 2017 and 2018 data.
If you’ve done some work using Excel, you’ll see that the formula I’m using here is the same.
Once you hit enter, you can go through the numbers and see that as you work your way through the rows, the numbers make sense. That is, until you reach the Total at the very bottom.
You’ll notice that it shows the same Total as the Total Revenue column even if the data throughout the rows are different. This is definitely not the result we’re looking for.
To understand why this happened, you also have to understand the context. Here, the context only lies on the rows preceding the Total where it chooses the lower amount between the two columns. But at the end, it simply compared the two totals from 2017 and 2018, picked which one was the minimum amount, and took that as the result.
It doesn’t understand that you want the total of all the Minimum Revenue listed throughout that column.
Using SUMX To Fix The Total
This is where iterating functions come in. For this example, I’m going to use SUMX.
Using SUMX as the iterating function, I can start working through the table virtually.
I’ll start by using the SUMMARIZE function for the Customers. Then, I’m using the MIN function to get the minimum value for the Total Revenue for 2017 and 2018.
When you use iterating functions, the calculation happening at every single row happens virtually.
All of these calculations are stored into memory, after which the SUMX function adds all of that stored data to come up with the right total at the bottom of that column.
In other words, it finally adds more context to make the system understand what kind of result you’re looking for.
***** Related Links *****
Iterating Functions In DAX Language – A Detailed Example
Working With Iterating Functions In DAX
Using Iterating Functions SUMX And AVERAGEX In Power BI
Conclusion
With this tutorial, hopefully, you were able to see how to fix Total issues you’ve been encountering with Power BI. The key here is understanding how iterating functions like SUMX work. Remember that these functions can be used to virtually work through each row of data, and you can get the right results.
So, if you get stuck on a Total in Power BI, you can play around with techniques like this. See if you can work in some parameters that follow the same context we applied to retrieve the value that you’re looking for.
All the best.
Sam
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events
