Direct Lake Rounding Issue

[Franaman]

i All, 

I need some help, maybe it is something simple but yet I am struggling.

I have in SQL a result of six rows with a value of 16.105 tons each and that gives you 99.63, but in Power BI it displays 16.110 and this gives you 99.66. It is rounding the figure but no matter what I do in terms of decimals, data types or ROUND dax it still rounds it off. This is a problem because when you have thousands of tons these small amounts add up. Please can someone help?

I am using Direct Lake Semantic model for more info.

[Franaman · Accepted solution]

Hi All, so I found the issue, it was in SQL , I needed the value to be decimal(18, 6) instead of 2 this fixed the problem in Power BI. But thanks for all the input. So the value needs to be raw in SQL and a proper ampunt of decimal spaces

[v-saisrao-msft]

Hi @Franaman,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[Franaman]

Hi There, I did do all that was mentioned, but still Power BI rounds before summing, I simply cannot figure out why, I understand the other comment belwo but I am using a Direct Lake Query so this maes things a bit different

[v-saisrao-msft]

Hi @Franaman,

In addition to the helpful response provided by @nilendraFabric, I would like to add few additional points that may assist you. 

1. Please verify the data types in the Direct Lake Semantic Model and ensure they precisely align with the SQL data types.
2. Please ensure that the aggregation settings for the column in your visuals are configured to Sum or Average, as required. Avoid using other aggregation methods that could impact rounding accuracy.
3. Once the data is loaded into Power BI, please verify the data type of the column in the Data view and ensure it is set to Decimal Number.

If this helps then please Accept it as a solution and dropping a "Kudos" so other members can find it more easily.

Hope this works for you!

Thanks.

[nilendraFabric]

Power BI and SQL might handle floating-point arithmetic differently. SQL often uses fixed-point arithmetic for decimals, while Power BI uses double-precision floating-point numbers, which can introduce small rounding errors.

Give a try to precision method shared in this article and see if this helps 

https://gorilla.bi/power-query/precision/

Thanks 

[Franaman]

hanks for this, the issue is I am using Direct Lake Query, I have tried almost everyhting at this stage, SQL is good and excel sums it perfect, it seems that Power BI is rounding then summing....