SUM differences between SAP HANA and Power BI

[T4S3R]

Hi everyone, 

i hope i am right here with this question.

I am currently trying to solve calculation differences between SAP HANA and Power BI, on the lowest possible aggregation level, the values match exactly (i already tested this for all rows). Here is an example of what it looks like:

When i display these values on a more aggregated level, the numbers suddenly drift appart:

The actual summarization in Power BI works regularly, it just sums up the values. The SAP HANA Database is supposed to just sum up these values as well. If i export the unaggregated values from the SAP HANA Database, it matches the data in Power BI. 

Has anyone ever had this problem / knows how to solve it? Does Power BI round after a certain number of decimal places within its sum calculation?

Best regards and thanks in advance!

Timo

[T4S3R]

Hi everyone,

after talking to the Microsoft Support and getting some feedback we figured out that the error comes from the currency conversion.

The HANA Database aggregates values to the desired granularity and then applies a currency conversion. The Power BI report recieves the lowest granularity and therefore the HANA Database applies the currency conversion on the lowest level instead of on the aggregation.

We still could not figure out a definite solution to solve the problem. To alleviate it at least a little bit it is possible to increase the decimal places to a higher number (8 is enough in our case e.g. 100,12345678). This way the differences get smaller.

I hope this helps someone in the future, if we find a definitive solution i will come back to this post again :).

Best regards and thank you all for your help!

Timo

[amitchandak]

@T4S3R , this can happen when there is an issue with the number of decimal places. Can check any values which getting rounded off any side before addition

[T4S3R]

Hi @amitchandak thank you for your answer!

Can i set the number of decimal places inside the query editor? I only know that you can select decimal number to keep as many decimal places as possible. 

[Greg_Deckler]

@T4S3R - Definitely looks like a precision thing based on what you are showing in the image. Hard to really troubleshoot though. You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

[T4S3R]

Hi @Greg_Deckler  thanks for the fast reply! 

I will definitely look into your ideas :). i already thought that troubleshooting is quite difficult, i couldn`t figure it out as well...

best regards

Timo

[Greg_Deckler]

@T4S3R - I know it may seem dumb, but try increasing the decimal precision for display for that column/measure in Power BI under Column Tools or Measure Tools.

[T4S3R]

@Greg_Deckler no idea is stupid for me at this point 😄 Thank you for your idea!

I already tried that and went up to 8 decimal places while the SAP HANA Database only delivers a max of 4. I still get the same summarization.

[tex628]

Try bringing in more dimensions in the aggregations when you compare, it might lead you closer to identifying the cause of the diff. I usually go step by step, adding more dimensions untill I can identify the individual transactions causing issues. 

/ J

[T4S3R]

Hi @tex628 thanks a lot for your reply 🙂

The differences diminish further and further the more Dimensions i take into account. The differences are quite visible at the most aggregated level (as seen in the original post) but after 2-3 Dimensions the differences get smaller and smaller.

I already thought it is a rounding error but the Datasource itself only delivers 4 decimal places which all show up in Power BI.

When I use direct query, I get exactly the same results as in the Datasource. The error only arises whenever i use import mode.

The performance of the Datasource is quite bad so direct query is no option unfortunately....

Best regards

Timo

[tex628]

I'm getting the vibe that this is an issue that should be handled from side of MS through a ticket, as @Greg_Deckler previously proposed. As it's clearly visible in the images, Power BI is importing 4 decimals as well which makes me think that the issue is more complicated and technical that a simple decimal import issue. But I'm very front-end oriented so I'm not going to be much help when it comes to diving deep into query behaviour. 

My advice is to build a very solid case, arguing that there is something problematic with import mode. Be careful putting too much weight on the comparison with BO. As it's not an MS system it might be hard for the supportstaff to verify the validity of the data even through you know that it's correct. I would proberby focus more on the diff between import and directquery, and identify a diff in the data on the lowest possible dimensional level.

Br,
J

[Greg_Deckler]

@T4S3R - Well, if all else fails, You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".