Rounding problem. Zero is actually 0.00000000004

[michaelsh]

If I format my measure with 12 decimal places, I see numbers such:

0.000000000004 (should be 0)

-19.999999999998 (which is -20)

I doubled checked, my measure is decimal with 2 decimal places.

I also dbl-check my column, which the measure sums - it is also a decimal with 2 decimal places.

I also added Round step in Power Query to 2 decimal places.

Still facing this issue.

My measures often check for zeros, so IF(Measure=0,true(),false()) returns FALSE while it is 0.

I solved it for now by doing >-0.01&&<0.01 but why is it like this?

[AllisonKennedy]

@michaelsh Rounding in Power Query will work, but formatting the measure to 2 decimal places does nothing to the actual value. It just displays it to 2 decimals but does not truncate/round the value. 

What is the formula for your measure? If you're doing any sort of average or complex calculation that results in decimals, you may get non-zero values.

You can use the DAX ROUND https://docs.microsoft.com/en-us/dax/round-function-dax function (or ROUNDUP or ROUNDDOWN depending on what you want). 

[michaelsh]

Rounding in Power Query to 2 decimal places didn't work either.

My measure is a simple SUM(Column)

I would prefer not to use ROUND() in my measures - it looks to me quite nonsence to remember to do it each time. There has to be an explanation to this, so I can fix the root of the problem.

Please help

[AllisonKennedy]

@michaelsh  Sorry I wasn't clear. Rounding in Power Query works only for the column. Once you create a measure, it will need rounded again. You cannot access measures in Power Query, therefore these cannot be rounded. Power BI Order of Operations 

It does seem strange that this would happen on a SUM measure - is there absolutely no other calculation happening in the report anywhere? I would be keen to see what's happening - are you able to replicate the problem with a dataset and report you can share via onedrive please?