Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Why Your Total Is Incorrect – A Key Power BI Concept

No comments

I want to dive into one key Power BI concept that could be causing incorrect totals. Although there are different scenarios, there is a simple way to fix this problem.

The key thing is understanding why the total is incorrect.

Sample Data Using Total Revenue

For this example, I have the Total Revenue for every product being sold over a specific time frame.

power bi concept

But what if I only want to see the Total Sales from my top 3 customers?

To do that, I am going to create another measure to reflect the required numbers. This is also a good way of showing some ranking calculations.

power bi concept

I’ll start with the CALCULATE function since I’m going after the Total Revenue. But I also want to change the context here by only looking at the top three customers. This is why I’m going to use the TOPN function.

power bi concept

TOPN will help me change the context for the entire calculation since I’m only looking for the Total Revenue of the top 3 customers. This function will add a virtual table within the CALCULATE function.

Out of habit, I’m using the VALUES function for the Customer Names. VALUES helps me understand better what’s going on in my measure, especially for more complicated reports. Note, however, that using VALUES is not a requirement in this case.

From there, I’m going to look into Total Revenue and then use the DESC function.

Once I put that measure in, I can see the sales broken down per product, but this time, for the top 3 customers under the column Top 3 Customer Sales.

power bi concept

Now, here’s where the issue starts. Under Top 3 Customer Sales, the total comes up to only 555,825.30. This is obviously incorrect.

Why is it showing an incorrect total? It’s because there’s no context coming from the Product Name going to the Top 3 Customer Sales. This means that although each row is showing the right number, when it comes to the total, only the Total Sales for the top 3 customers overall are showing.

What I want to happen is for the Total Sales for the top 3 customers for each product to be shown per row, then for each of those totals to be added up in the end.

Fixing The Incorrect Total

To solve that problem, I’m going to create a new measure and call it Top 3 Customer Sales FIX.

power bi concept

I’ll start out with SUMX. Remember that SUMX is an iterator, which means that it adds logic to every single row of the table. It also saves each of these results into the memory and then calculates these in the end.

Then, I’ll create a virtual table by using the SUMMARIZE function and reference the Products by Product Name. Next, I’ll create an additional column in the virtual table called Top 3. From there, I will reference the prior formula that I just created, which is the Top 3 Customer Sales FIX.

I basically rebuilt the column Top 3 Customer Sales, but this time, with the right context at the total.

This is what happens when I bring that new measure into the table.

The total now shows the correct number. Again, this has been fixed because we added context not only for each row of product, but also to the total.

I can now remove the previous column showing the incorrect total so that only the right data will be shown.

Conclusion

Hopefully, this technique will help those who are having problems with incorrect totals on their reports. This is especially useful when there is complex logic applied.

Note that this is not the only way to fix incorrect totals. In some cases, more complex solutions are required. Again, it all depends on what’s causing the incorrect totals to show up.

Once the cause of the issue is identified, it’s easy enough to figure out what sort of context is missing to correct the results.

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

***** Related Links *****
Fixing Incorrect Totals Using DAX Measures In Power BI
Running Totals in Power BI: How To Calculate Using DAX Formula
Using Advanced Logic In Power BI To Correct Your Totals

***** Related Course Modules *****
Advanced DAX Combinations
DAX Formula Deep Dives
Mastering DAX Calculations

***** Related Support Forum Posts *****
Unique Dax Grand Total Incorrect
Incorrect Totals (Aggregated Full Amount)
Incorrect Total In Power BI
For more incorrect total support queries to review see here…

Enterprise DNA Events


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.