Forum Discussion

bankit710's avatar
bankit710
Regular Visitor
8 years ago

Rounding decimal numbers

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.

 
Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    If this is M (power query) then you need to add an additional parameter in the Advanced Editor:

     

    https://msdn.microsoft.com/en-us/library/mt253380.aspx

     

    According to that article, it is applying "If roundingMode is not specified, RoundingMode.ToEven is used." which is kind of a stupid default, I'll give you that. To fix, see below:

     

    See this code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzMjNVitWJVjLWM7GAsIBixhCWqZ6ZJYRlomdqhmCZwFnmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Unrounded = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unrounded", type number}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Unrounded", "Unrounded - Copy"),
    #"Rounded Off" = Table.TransformColumns(#"Duplicated Column",{{"Unrounded - Copy", each Number.Round(_, 2), type number}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Rounded Off", "Unrounded", "Unrounded - Copy.1"),
    #"Rounded Off1" = Table.TransformColumns(#"Duplicated Column1",{{"Unrounded - Copy.1", each Number.Round(_, 2, 0), type number}})
in
    #"Rounded Off1"

    The second round returns tie-breakers of 5 rounding up.

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Are you seeing this in M or DAX? Because in DAX, if I create a column:

     

    Column = ROUND(Rounders[Unrounded],2)

    Everything rounds correctly.