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 ConceptsUsing Iterating Functions SUMX And AVERAGEX In Power BIWorking 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

[youtube https://www.youtube.com/watch?v=PbkWkyKtAEw?rel=0&w=784&h=441]