Dax formula to round to the nearest 10th

[shophoney]

Hi I have a discount column that i would like to round the values to the nearest 10th or to 5th.

So a result of 24.9 = 25%

21.2 = 20%

57.1 = 55%


=MROUND(1-('SALES TABLE'[NET RETAIL]/'SALES TABLE'[RETAIL]),.1)

​Thanks

 

[estevaoba]

Try this:

=MROUND(1-('SALES TABLE'[NET RETAIL]/'SALES TABLE'[RETAIL]),0.05)

Godspeed!

 

[shophoney]

Hi I just tried that and I now get an ERROR in the column?

 

[estevaoba]

Hi!

I don't get it.
I've done several tests here and it works fine.

For example, 1-(35/49)=0,285714
Then I do: =MROUND(1-(35/49),0,05) and it results 0,3, or 30%.

Select the cell with error, then try shift+F3 for the formula dialog box. Then change an element in the formula and watch results.

Godspeed!

 

[shophoney]

Hi!

I don't get it.
I've done several tests here and it works fine.

For example, 1-(35/49)=0,285714
Then I do: =MROUND(1-(35/49),0,05) and it results 0,3, or 30%.

Select the cell with error, then try shift+F3 for the formula dialog box. Then change an element in the formula and watch results.

Godspeed!

"Calculation error in column 'SALES TABLE'[]: An argument of function 'MROUND' has the wrong data type or the result is too large or too small."

 

[tallan]

If you click on the error icon in the column you should get an explanation for the error. What does it say?

 

[shophoney]

This is the message i get. It shows ERROR and more info is :"Calculation error in column 'SALES TABLE'[]: An argument of function 'MROUND' has the wrong data type or the result is too large or too small."

Could it be from a divide error.

If the 2 values are equal?

=MROUND(1-('SALES TABLE'[NET RETAIL]/'SALES TABLE'[RETAIL]),0.05)

 

[tallan]

Excellent idea! Another possibility is that the division is returning a negative number (with MROUND, both the number to be rounded and the multiple need to have the same sign (both must be positive or both must be negative, check out the last example in the documentation here).

Tom
PowerPivotPro - Transforming your Business with Power Pivot and Power BI

 

[shophoney]

Hi still having issues with rounding.

I found this old formula i used before. But I need to round to the 10th. IE 10%, 20%...

=iferror(round(if(OR([NET RETAIL]=0,[NET RETAIL]<0),0,if([NET RETAIL]<1,1,(1-([NET RETAIL]/(-[DISCOUNT]+[NET RETAIL]))))),1),0)

Thoughts?