Ever built a table containing measures in Power BI, done a quick sanity check on your table totals and noticed they’re incorrect? Let’s explore why this may be.
Here we have a simple table, built from data in the AdventureWorks database. It shows the three product categories, the Total Sales and Average Order Total of each category:
As you can see, the Total Sales column adds up perfectly, but the average is just plain wrong. 19.42 + 1,862.42 + 37.33 = 1,919.17, not 486.09
Adding more data to our table, begins to show why Power BI is showing 486.09 as the total:
If you look at each row, using the actual values used in the calculation, which is:
Total Sales / Count of Sales = Average Order Total
we get
28,318,144.65 / 15,205 = 1,862.42
The same is true for the Total row:
The total Average Order Total is calculated like any other row, it is calculated against all the rows in the table.
29,358,677.22 / 60,398 = £486.09
…not by calculating the sum of the average column shown in the visual.
This demonstration shows it best, by removing the columns that split out the data, the total remains the same, showing that the total is calculated globally and ignores the line-by-line calculations:
Here’s another example, let’s use some logic to filter out certain values. In this case, only Total Sales per category where the category has a sales total of more than £1,000,000 are required.
On the surface this makes no sense at all, how can the total of one value be more than the value itself?
Again, adding more visibility means it starts to make sense:
The Total row is calculated using the same logic as the other rows, in this example:
- Accessories total is less than £1,000,000 so no data is shown
- Bikes total is more than £1,000,000 so the data is shown
- Clothes total is less than £1,000,000 so no data is shown
- Total of all 3 is greater than £1,000,000 so the data is shown for every value that makes up the total
In Summary..
The key thing to remember is to treat the “Total” row as an independent row, doing its own calculation, rather than a total of the visual numbers you see in the columns.
Thanks, does this apply to both Matrix and Table Visuals only? Also, do you perhaps have a PBIX you can share please?
It would be a great addition to this post to provide a DAX solution (or solutions) on how to calculate a correct total of the averages. Many people would benefit. Thanks!