# Dax formula to round to the nearest 10th



## shophoney (Aug 28, 2017)

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 (Aug 28, 2017)

Try this:

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

Godspeed!*


----------



## shophoney (Aug 28, 2017)

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


----------



## estevaoba (Aug 28, 2017)

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 (Aug 28, 2017)

estevaoba said:


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



_"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 (Aug 29, 2017)

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


----------



## shophoney (Aug 29, 2017)

_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 (Aug 29, 2017)

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 (Aug 29, 2017)

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?


----------

