How to resolve floating numbers in PQ - addition

[non23]

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?

[zenisekd]

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.

[ronrsnfld]

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

[v-heq-msft · Accepted solution]

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

[m_dekorte]

@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]

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]

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]

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]

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