How Power BI Totals are Calculated

When using Power BI visuals such as a table or matrix, they use a built in total column for your rows or columns.

When using it, you may have noticed that the totals column sometimes doesn’t add up to a Grand Total as might be expected. It sometimes looks like the below screenshot:

From first glance, you can see that the “Total” column isn’t the sum of its row, as you might expect.
So why is this the case?

The “Total” column doesn’t just add up the rows, it recalculates the measure again but in the context of that entire row.
That context is usually to ignore the row-level breakdown, but instead evaluate the DAX formula for the entire row.

To show this, let’s look at the measure that makes up those values in the screenshot:

 Average Quantity = AVERAGE(‘Product'[Quantity])

The measure calculates the AVERAGE for the quantity of a product, and in the case of this visual, by date.

Because the measure calculates the AVERAGE, the “Total” column uses that context when recalculating to create the ”Total” column. Thus, resulting in creating the AVERAGE for the whole row.

This scenario is common for non-additive measures such as Percentages, Averages, Time Intelligence Calculations etc.

Best thing is to remember is that: Totals column = re-evaluation, not a sum of row outputs.

Tags: Calculate, DAX, Power BI