# Why Your Total Is Incorrect – A Key Power BI Concept

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. You may watch the full video of this tutorial at the bottom of this blog.

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.

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.

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.

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.

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.

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

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.

## Finding Patterns In Your Data Using Internal Logic in Power BI

Today I’m going to showcase how you can discover patterns in your data when using Power BI. This is an...

## Report Builder Tutorial: Sort & Filter Data In Paginated Reports

In this Report Builder tutorial, you’ll learn how to sort and filter the data in your paginated...

## Enterprise DNA October Updates – Milestones, Courses and More

It's the final quarter of the year and we're all set to end 2021 with a bang. We're proud to say that...

## Creating A Pareto Chart In Power BI – Advanced DAX

This blog tutorial showcases a really interesting example around Pareto analysis. I’m going to teach...

## Power BI Tooltips On Report Images

With the recent Power BI challenges that we run in Enterprise DNA, I've become a huge fan of the Power...

## Best Practices In Using The Q&A Feature In Power BI

The Q&A feature or the natural language feature is seriously one of the most incredible features...

## Perpetual Holiday Calendar – POTW #12 (Power Query Solution)

In this blog post, we'll explore my solution to Problem of the Week #12. For this challenge, we...

## What Is DAX Studio In Power BI Desktop

This tutorial will discuss what DAX Studio is and how it can be used to optimize your DAX codes. DAX...

## DAX Queries Using Variables And Important Functions

This tutorial will discuss about variables, including the FILTER and CALCULATE functions. You’ll...

## Dynamic Tooltip In Power BI With Embedded Charts

I want to show you an awesome trick for the visualizations in your reports. You can use a dynamic...

## Power Query Features: An Overview

This tutorial will discuss about the available features inside the Power Query Editor. You'll learn how...

## Power BI P&L Statements: Challenges And Solutions

Profit and Loss Statements are often challenging to create in Power BI, especially if you're working...