Round function in query editor

[bankit710]

i have found a problem with ROUND function.

what i was doing is as follows

i have one price column, one quantity column in my table

i am getting data from dynamics RMS where at the POS, customers will always get charged in this format xxx.xx (x is number 0-9)

initially RMS is rounding up PRICE*QUANTITY by 2 decimal numbers

POWER BI is sowing different amounts than RMS itself

6 hours spend on a table with 10M rows.

FINALLY I FOUND THIS

PRICE (0.75) * QUANTITY (2.3) = 1.725 (UNROUNDED)

NOW ON THIS RESULT I tried using TRANSFORM->ROUND with decimal number=2

i found result = 1.72----------

basically it didnt round the number. CORRECT RESULT = 1.73 (according to other softwares that exist)

HERE IS THE PATTERN THAT I HAVE NOTICED.

if a number has 3 decimal digits (1.XXX) and if that last X=5. the number will not be rounded.

For Example: 1.265 3.485 6.895 1.235 5.695 4.565

I dont know why it is doing that because those two columns containt numbers only. no reason for power bi to get cunfused.

lucklly i found this becuase i was cross checking my report with my initial software (dynamics RMS)

looks like a small number but for a company who is adding 500K rows of data into that table eberyday. the change in final amount will be big.

[v-sihou-msft · Accepted solution]

@bankit710

In Query Editor, the Rounding is actually calling Number.Round() function. It has a third argument to sepcify round mode. In your scenario, you should use 0 or 2. 

For more details, please see: https://msdn.microsoft.com/en-us/library/mt253380.aspx