Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Fixing Incorrect Totals Using DAX Measures In Power BI

by | 9:00 am EDT | April 22, 2020 | DAX, 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

## How to Alternate Colors in Excel: 3 Top Ways Revealed

Knowing how to alternate colors in Excel can be a game-changer; there's little more satisfying than a...

## How to Join Data Sources in Tableau in 4 Simple Steps

Looking to join data sources in Tableau, but unsure where to start? You're in the right place. The...

## How to Get Rid of ABC Column in Tableau: 3 Quick Methods

It's time to get rid of the ABC column once and for all, we get it, it can be a bit confusing and...

## What is a Measure in Tableau? Understanding The Basics

So, you've started using Tableau and seen "Measure," but unsure about what it is and how to use it?...

## How to Forecast in Tableau: Top 2 Ways Explained

Ready to create a forecast in Tableau, don't worry, it's easy. To create a forecast in Tableau: Start...

## How to Show Top 10 in Tableau: 4 Top Methods Explained

So you're deep in the world of data visualization and want to show the top 10 results in Tableau? Well,...

## How to Group in Tableau: Top 3 Methods Explained

In Tableau, grouping is a technique used to combine similar items in a visualization. This process...

## Top 20+ Data Visualization Interview Questions Explained

So, you’re applying for a data visualization or data analytics job? We get it, job interviews can be...

## 10 Brilliant Sample Datasets + How to Create Your Own

Data is the lifeblood of the 21st century, and you need the good stuff for a project to succeed....

## Code Visualizer: It’s Time to Visualize Your Code

Have you considered the immense power of visually interpreting your code? As a cornerstone of practical...

## What is an Excel Binary Workbook? A How-To Guide

Hey there, Excel enthusiasts! Today, we're diving into something pretty cool and super useful—Excel...

## How to Create a Bin in Tableau: A Step-By-Step Guide

One of the features that makes Tableau attractive for analysts is its ability to easily create bins - a...