Grand Total of measure doesn't equal sum of row values

[kwahila]

Dears,

This is a fundamental question I am trying to anwser and am getting lost in the forums with different ideas. I have a measure that I put into a table. When the grand total of each column is turned on for the table, it does not actually equal the sum of the individual elements in the rows. It is just too strange. Why should the way the measure is written impact the fact that the grand total should just be the simple sum? Please help?

Regards,

Kevin

[AlbertoFerrari]

Most likely cause is that you turned on bidirectional filtering on some relationship. Bidirectional filtering is very powerful, but it is also very dangerous, as its behavior is non-additive in most scenarios and not-so-easy to understand at first sight.

Try to turn it off, and let me know if that was the case.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[Anonymous]

Ciao @AlbertoFerrari and @marcorusso ,

I have a similar scenario when using a LASTNONBLANK function for a closing balance where the total for the visual doesn't equal the sum of the detail rows, but it's only off by a small margin, i.e. < 1%, each time.  This leads me to believe that it's an order of operartions or data type issue, but everything is in a decimal format.

For example the visual below shows a total of $225,441,095.23 for the following measure:

Total (USD) = calculate(SUM(AR[total_usd]),LASTNONBLANK(AR[effdate],CALCULATE(SUM(AR[total_usd]))))

However, the actual total when adding all of the InvDate rows equals $225,441,095.25! So there is a difference of $.02! This is occurring intermittently but it's never a large margin of error.

The raw data includes columns from the the AR table: [total usd], [InvDate], and [EffDate] per the measure for the closing balance. Basically, I want to total the balance per [EffDate] and/or [InvDate] where the closing balance is based on the [EffDate].

Is there something I'm missing or is this a Power BI Bug?

Thanks,

Ben

[marcorusso]

Check the data type of the column you are summing up. WIth Fixed Decimal Number (which is like MONEY in T-SQL) this shouldn't happen. With Decimal number (which is like DOUBLE in T-SQL) this could be a normal rounding issue. It doesn't seem to be related to LASTNONBLANK, though.

[Anonymous]

Thank you, @marcorusso ! It was indeed related to the data type and/or rounding, since the raw data had a precision of four decimal places but it was only showing two in Power BI.

Thank you,

Ben

[TomMartens · Accepted solution]

Hey,

there are some reasons why the measure shows not the "simple" sum of the values from the column, this is often due some filtering of the rows aggregated by measures. It could be possible that the measure on the Grand Total aggregates more rows than the detail row.

Here you will find a simple pbix file, that shows some average weirdness. The measure Simple Average just calculates the rows, this leads to a Grand Total value that is in a way correct, but maybe not expected. The Measure "Not So Simple Average" shows the same values on the detail level (the rows of the table visual), but for the Grand Total it calculates the average of each Category.

Another reason could also be the aggregate function DISTINCTCOUNT(...), it counts the distinct value of a column available in the rows that are filtered. If there are Month on rows in the same customer buys something in each month, the value will be 1 for each month but not 12 for the Grand Total.

So, without having more information about your measure and the structure of your table or matrix visula, it's just guesswork.

Hope this gets you started

Regards

Tom