Issue with handling Decimal data Types

[akash_mohan1810]

Hi,

I have 2 columns Value and qty both of decimal data type formats. In whole Data range maximum scale of value is 2 and for qty is 3. I have to calculate Cummulative of value and qty and divide them. for eg in my data qtys are like {2.454,234.56,0.01}, as maximum digits after decimal is 3 so when i sum it up result value scale should not exceed 3. In PowerBI when i check the sum its like 1.46E- 11, im not sure how is this possible. to avoid this in power query i grouped up with below mcode

= Table.Group(
#"Changed Type",
{"ID"},
{
{"value", each Number.RoundUp(List.Sum([Value]), 2), type nullable number},
{"Qty", each Number.RoundUp(List.Sum([Qty]), 3), type nullable number}

}
)

result values are 0.1 and 0 which is trustable. My question here is how to achieve this without grouping.

Please use the below drive link for the excel files and pbix used.

https://drive.google.com/drive/folders/1XbZgu07goVMzn6Kj8soK2bCP_Xzayhf2?usp=sharing

Attached images of source file and pbix file with both scenarios one grouped in power query and another table without grouping.

@amitchandak @Sahir_Maharaj @Greg_Deckler @Ashish_Mathur @Martin_D @Jihwan_Kim 

Thank  for ur patience and reply for this post.

Akash

[Martin_D]

Try changing the column to fixed decimal type as the last step in Power Query. It's called Currency in Power Query and hast 4 decimal points. According to your description this matches with your requirements.

[akash_mohan1810]

Thanks Martin,

To be surprised, even for Fixed decimal same results are thrown from PowerBI.