Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Adjust numbers

Hi community, 

 

Have this thing to fix: I have a column of decimal values and I would like this to happen:

 

235,24 > 235

207.24 > 209

159.45 > 159

371.80 > 375

343.92 >345

 

in other words, adjusted numbers always have to finish in 5 or 9. 

 

I tried with INT or round Up but doesn't always work.

Do you know any other way?

 

thank you

Alberto

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    7 years ago

    HI Anonymous

     

    I think I follow what you need and I think this calculated column gets close.  You can tweak the IN statement to control if you prefer Zeros to round to 5 or 9.  I have attached a PBIX file

     

    Column = 
VAR BaseVar = INT('Table1'[Column1])
VAR NewEndVal = SWITCH(TRUE(),MOD(BaseVar,10) IN {0,1,2,3,4,5} , 5 , 9)
RETURN (INT(DIVIDE(BaseVar,10)) * 10) + NewEndVal

1 Reply

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    7 years ago

    HI Anonymous

     

    I think I follow what you need and I think this calculated column gets close.  You can tweak the IN statement to control if you prefer Zeros to round to 5 or 9.  I have attached a PBIX file

     

    Column = 
VAR BaseVar = INT('Table1'[Column1])
VAR NewEndVal = SWITCH(TRUE(),MOD(BaseVar,10) IN {0,1,2,3,4,5} , 5 , 9)
RETURN (INT(DIVIDE(BaseVar,10)) * 10) + NewEndVal