# Fixing Incorrect Totals Using DAX Measures In Power BI

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.

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 OnlineAdvanced Analytics in Power BI.

Sam

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## R Scripting For Power BI Using RStudio

In this blog, weâ€™re going to go through the basics of R scripting for Power BI using RStudio. The...

## Simple Aggregations In Power BI

In this blog post, I'll touch on some simple aggregations in Power BI that you can use in your DAX...

## Microsoft Report Builder: Steps In Creating A List

In this tutorial, you will learn how to create a list and page break in Microsoft Report Builder. A...

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...

## Power BI Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit...

## Understanding Power BI Aggregations

Now, let's delve deeper into one of the most important concepts in Power BI calculations â€” the...

## Using Python In Power BI | Dataset And String Function

In this tutorial, we'll discuss how you can create and prepare Dataset and String Function using Python...

## Announcing The Enterprise DNA Submit A Showcase Program

Today we are launching a brand-new program here at Enterprise DNA. We are looking to collaborate with...

## Inventory Management Reports To Show Trends In Sales

This Enterprise DNA Power BI Showcase focuses on Inventory Management. You may watch the full video of...

## Huff Gravity Model Analysis in Power BI

In this tutorial, we'll learn how to do a Huff Gravity Model analysis in Power BI. We can use this...