SUM in PowerBI not rounding correctly

[Anonymous]

Hi, 

I have a column that is summing the lines of an order together, and then i am rounding this decimal to two decimal places. Seems fine. 

Power BI however, when using the inbuilt SUM feature produces this: 

Where the value it is rounding up is 41.895 - therefore in rounding this should be 41.90.  (the bottom row)

I therefore tried to create a measure that calculates the sum of an order based on the order number, and rounds it to two decimal places - however this also rounds 41.895 to 41.89 

TotalGrossPerOrder = ROUND(SUMX(VALUES('fact Orders'[PurchaseOrderNumber]), CALCULATE(SUM('fact Orders'[LineGross]))), 2)

 (second from bottom row)

The only way i seem to be able to get it to round correctly, is on this measure force the decimals to three places: 

But obviously this then gives me the figure with a 0 on the end: 41.890. 

Any ideas what i'm doing wrong? 

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I assume that you want to round up the measure. You may try the roundup .

Measure = ROUNDUP(SUM('Table'[Val]),2)

Result:

Best Regards

Allan

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

[tex628]

Can you provide a picture of the amount of all the rows contained in that order, in the data view (See below)? Please also modify the decimals in the column so that you show atleast 5 decimals. 



Br,

J

[Anonymous]

Hi, So i'm using direct query - and for that order there is only a single transaction - so it isn't actually having to sum anything. 

Five decimal places: 

41.89490 is not rounding correctly - it should be 41.90 not 41.89

[GDUb]

The rounded value of 41.89490 is 41.89 using standard rounding calculation.  .00490 is less than 0.005, so it rounds down to zero at two decimal places. 0.0050 and greater rounds up to 0.01

[tex628]

After consulting a colleague I was told the rounding should be correct :). When rounding you consider all decimals -> 

10,645
0,0005 Rounds up to
10,65

10,645
0,0045 Rounds down to
10,6

So in the second example you round from 0,0045, not 0,005. 

If you want any other type of logic you will need to convert the actual column before the automated rounding happens. You can do this by identifying where the decimals create this situation, XXX.XX4(5-9) (3rd decimal is a "4" & 4th is 5 or higher). Then change the actual values to allow the rounding to work as you desire. 

Br,
J

[tex628]

I believe this has to do with direct query. I've been able to recreate your problem and I'm running a few tests. I'll get back to you. 

/ J