Forum Discussion

non23's avatar
non23
Icon for Helper I rankHelper I
1 year ago
Solved

How to resolve floating numbers in PQ - addition

Hey guys, I'm trying to add two columns to the PQ.
Sales: 1012.55
Tax: 101.26
Total:  1113.81

but when I add a custom column for the Total it will show as 1113.80499999999.
How can I fix this to show 1113.81 instead?

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi non23 ,
    Based on your description, it's not clear how your TAX column is formed; if it's said to be derived from a ratio multiplied by sales, it's easy to see why you have so many decimal places. And based on your results, rounding would end in either 8 or 805, it wouldn't round up plus one. So you might try using Number. round() twice.

    = [Sales] + Number.Round(Number.Round([Tax],3),2)

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

8 Replies

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    1 year ago

    non23 

    You can use a precision type within a Value expression, for example:

    Value.Add([Sales], [Tax], Precision.Decimal)

     

    I hope this is helpful

    • non23's avatar
      non23
      Icon for Helper I rankHelper I
      1 year ago

      I tried following it but the result is still giving 3 decimal places. Also changed the format to fixed decimal number still the result is 3 decimal places.

      • m_dekorte's avatar
        m_dekorte
        Icon for Resident Rockstar rankResident Rockstar
        1 year ago

        Hi non23,

        Alternatively, instead of using a precision type, you can go for a different approach. Using a rounding function like Number.Round, and a RoundingMode type.

         

        Number.Round( [Sales] + [Tax], 2, RoundingMode.AwayFromZero )

         

         

        I hope this is helpful

  • zenisekd's avatar
    zenisekd
    Icon for Super User rankSuper User
    1 year ago

    Hello, simply select fixed decimal number type.

    Or in the custom column, add the ", Currency.Type" code. 

    Cheers.

    Kudos and mark as solution appreciated.

    • non23's avatar
      non23
      Icon for Helper I rankHelper I
      1 year ago

      Changed the format to fixed decimal place still the result is 3 decimal places.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi non23 ,
    Based on your description, it's not clear how your TAX column is formed; if it's said to be derived from a ratio multiplied by sales, it's easy to see why you have so many decimal places. And based on your results, rounding would end in either 8 or 805, it wouldn't round up plus one. So you might try using Number. round() twice.

    = [Sales] + Number.Round(Number.Round([Tax],3),2)

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    1 year ago

    Use the Currency.Type (Fixed Decimal) for your columns, and also Round your tax computation away from zero.

  • zenisekd's avatar
    zenisekd
    Icon for Super User rankSuper User
    1 year ago

    Just out of curiosity, why does it bother you in Power Query. You do know, you can adjust the decimal places in Power BI on the column format settings? There you can set how the number will be displayed. 

    If you round the numbers in Power Query, it might lead to incorrect numbers in the end.