Getting totals correct inside Power BI reports can sometimes be the most frustrating thing when utilizing DAX measures. You might probably get into certain situations where your Totals for DAX measures aren’t adding up the way you want them to. You may watch the full video of this tutorial at the bottom of this blog.
DAX measures can be based on standard aggregation functions, such as COUNT or SUM. These DAX formulas enable you to perform data modeling, data analysis, and use the results for reporting and decision making.
In this blog, we’re going to discuss a grand total logic concerning DAX measures. This concern was raised in the ENTERPRISE DNA support forum. If you want to review the question and have a look at the exact scenario from the forum, just click the provided link below.
https://forum.enterprisedna.co/t/grand-total-of-branch-measure/206
I’ll be showing you the technique that was used to solve the scenario.
Certainly, you can apply this technique to various scenarios, especially when creating some quite complex DAX logic or when branching out multiple measures. This will also help you solve your totals that may be calculating incorrectly.
Sample Problem Scenario
Here’s a generic example that is somehow related to the discussed scenario in the forum.
Now, we’re looking at the Sales result within this table. Moreover, I’m comparing it to another result, which is the Sales Last Year.
In this example, we’re trying to find out the minimum result for every single customer based on the context that is selected. It can be any measure such as MIN Total Sales and Total Sales Last Year ( in this case, we’ll be using the generic ones).
Most probably, you’d think that the formula from the image below will give us the appropriate results.
Well, let’s drag the Minimum Testing measure to the table and check the results.
Looking at the the results, we can say that the calculations for each row were all accurate.
However, it has calculated the Total incorrectly.
It only calculated the minimum total using the MIN function for each row within the Total Sales and the Sales LY measures whenever there is no context on each calculation.
Furthermore, it didn’t count up the particular minimums in a unique way and get the sum of all these unique minimums via any context that we’re in.
Analyzing The Correct Results For The Sample Scenario
Now, let me show you the appropriate results for the previous scenario.
You’ll see from the image that it shows a very different result as it is evidently much less than all the other totals.
This makes much more sense because we are mainly counting up just the minimums of every single row in this table. So, that means there’s going to be a much lesser value for the result.
The primary logic that we need to understand here is to evaluate every single result using virtual tables.
Iterating through virtual tables allows you to make sure that the logic you implement is iterating through the correct context for the total versus any natural context that might occur via selections made on a report page.
If you can start understanding how to incorporate virtual tables inside your DAX functions, you will quickly see the opportunities to extend your analysis even further.
Creating Virtual Tables Within DAX Measures
Now, to solve the current scenario, the first thing that we need to do is to create a virtual table of every single customer with the help of the SUMMARIZE function.
The SUMMARIZE function returns a summary table for the requested totals over a set of groups.
Then, within that particular virtual table that we’re going to iterate through, the highlighted columns from the image below will be generated virtually.
Summing Up The MIN Of Each Row In The Virtual Table
As we get to the evaluation phase, we’ll be using an iterating function called SUMX. This function returns the sum of an expression which is evaluated for each row in a table.
In our case, the SUMX function will sum up the MIN result of every single row of the virtual table that we have created.
And that’s how we got the correct results.
Another cool thing about this is that the virtual table also works for every single row. This means that this table is only going to be one row long.
This is because this filter is only going to be virtually applied in the formula, and the Customer ID within the SUMMARIZE function is only going to be relevant to the customer context that we’re in.
And that’s simply how you can solve this particular scenario.
***** Related Links *****
How To Understand Virtual Tables Inside Iterating Functions In Power BI – DAX Concepts
Using Iterating Functions SUMX And AVERAGEX In Power BI
Working With Iterating Functions In DAX
Conclusion
To sum up, the key to understanding how totals are calculated is to learn in-depth how context works inside Power BI.
This technique is definitely usable because there are lots of situations where the totals just won’t add up to what you think or what they should be. For instance, you might actually get a total but you have no idea that it is actually calculating incorrectly.
The main thing that you need for this technique to work properly is to understand the virtual table methodology. You need to efficiently integrate these virtual tables into your DAX measures to make sure that they are getting the desired results.
This will help you solve not only the one that you’re currently working on but also the other future total issues that you might encounter when running calculations inside your Power BI models.
There’s quite a lot to apply and implement in your own environment with this. That’s the reason embedding these concepts in your mind is crucial.
For more advanced DAX techniques you can use in Power BI check out this module at Enterprise DNA Online – Advanced Analytics in Power BI.
Sam
