Forum Discussion
Power Query number rounding issue
- 4 years ago
1. =ROUNDUP(([@Cancelled]-[@ACCEPTED])*1440,"1:00") - "1:00" is a character, hence it will be treated as 0 in Excel. Hence =ROUNDUP(([@Cancelled]-[@ACCEPTED])*1440,0) will give the same result.
2. Now, below are results for [@Cancelled]-[@ACCEPTED] in Excel and when this is multiplied by 1440.
Below screenshot is from Excel
Now, you see the second result is 299.00000000023200. Excel's precision is 15 digits. Now, if you count 15 digits, the number would be 299.000000000232. Since Roundup is detecting 232 after decimal places, hence Roundup is making 299.00000000023200 to 300.
3. This behaviour is same in DAX also. Hence, DAX and Excel behaves the same.4. See the below for results of Current-Accepted (This screenshot is from Power BI Desktop)
Now let me use Windows supplied calculator for second row values
DAX - 0.207638888889051*1440 : 299.0000000002334
PQ - 0.207638888888889*1440 : 299.0000000000002
Now, in case of PQ when I count 15 digits, it comes out to be 299.000000000000. This is quivalent to 299, hence no effect due to Roundup.
Hope, it clears up the issue for you.
I will also suggest you to read Floating point Airtmetic issue of Excel - https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result
- 3 years ago
The second argument of Number.RoundUp is “digits” – the number of digits to round to. Replacing the 1 in your formula with a 0 will fix the problem.
Number.RoundUp(
Duration.TotalMinutes( [Cancelled]-[Accepted] ),
0
)
Trebor84
I am not sure If MattAllington meant the same as I tried this way
Number.RoundUp(Duration.TotalSeconds([Cancelled]-[ACCEPTED])/60)
Hi, that works apart from the rounding up.
In Excel the second example gives me 300 minutes which is what I am looking for using: =ROUNDUP(([@Cancelled]-[@ACCEPTED])*1440,"1:00")
- Vijay_A_Verma4 years agoMost Valuable Professional
1. =ROUNDUP(([@Cancelled]-[@ACCEPTED])*1440,"1:00") - "1:00" is a character, hence it will be treated as 0 in Excel. Hence =ROUNDUP(([@Cancelled]-[@ACCEPTED])*1440,0) will give the same result.
2. Now, below are results for [@Cancelled]-[@ACCEPTED] in Excel and when this is multiplied by 1440.
Below screenshot is from Excel
Now, you see the second result is 299.00000000023200. Excel's precision is 15 digits. Now, if you count 15 digits, the number would be 299.000000000232. Since Roundup is detecting 232 after decimal places, hence Roundup is making 299.00000000023200 to 300.
3. This behaviour is same in DAX also. Hence, DAX and Excel behaves the same.4. See the below for results of Current-Accepted (This screenshot is from Power BI Desktop)
Now let me use Windows supplied calculator for second row values
DAX - 0.207638888889051*1440 : 299.0000000002334
PQ - 0.207638888888889*1440 : 299.0000000000002
Now, in case of PQ when I count 15 digits, it comes out to be 299.000000000000. This is quivalent to 299, hence no effect due to Roundup.
Hope, it clears up the issue for you.
I will also suggest you to read Floating point Airtmetic issue of Excel - https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result