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.
***** 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
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
