Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rounding - If statement

Hi,   I'm wanting to round my data but I want the rounding to be an if statement dependent on the size of the number itself (jobs number). I can do this in Tableau using the formula in the stateme...
Need Help
  • judspud's avatar
    judspud
    6 years ago

    Hi Anonymous 

     

    The round function requires you to give it a number of digits to round to. 

     

    for example;

     

    ROUND(Value,0) would give you the value to the nearest integer.

     

    Hope this helps.

    George

  • JpTall's avatar
    JpTall
    6 years ago

    I often get this error when I have misplaced (or missed out) a bracket, and so it things you haven't given a 'then' or 'else' statement.

    Make sure you haven't missed a bracket after ROUND(SUM())

judspud's avatar
judspud
Solution Supplier
6 years ago

Hi Anonymous 

 

Are you wanting this as a column in the data or as a measure?

 

You can use either DAX or QueryEditor to create.

 

The following link will give you some advice on the DAX IF Statement

https://docs.microsoft.com/en-us/dax/if-function-dax

 

QueryEditor uses a slightly different language called PowerQuery

 

Thanks,

 

George

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi,

 

Thanks for the quick response!

I'm wanting it as a measure I think.

I want to be able to show rounded figures for the majority of my charts, but I also want to be able to use the percentages which means I need to use the raw data to calculate them.

 

I've had a look at the DAX page but when i've tried to write the statement out in PBI it brings back errors. Below is the measure i've tried to create but the error says 'Too few arguments were passed to the ROUND function...'. 

 

JobsRounded = IF(SUM('LA level data (unrounded)'[Jobs]) < 10, SUM('LA level data (unrounded)'[Jobs]),
IF(SUM('LA level data (unrounded)'[Jobs])<15,ROUND(SUM('LA level data (unrounded)'[Jobs])/10.0)*10,
IF(SUM('LA level data (unrounded)'[Jobs])<500,ROUND(SUM('LA level data (unrounded)'[Jobs])/25.0)*25,
IF(SUM('LA level data (unrounded)'[Jobs])<1000,ROUND(SUM('LA level data (unrounded)'[Jobs])/50.0)*50,
IF(SUM('LA level data (unrounded)'[Jobs])<10000,ROUND(SUM('LA level data (unrounded)'[Jobs])/100.0)*100,
IF(SUM('LA level data (unrounded)'[Jobs])<25000,ROUND(SUM('LA level data (unrounded)'[Jobs])/500.0)*500,
IF(SUM('LA level data (unrounded)'[Jobs])<250000,ROUND(SUM('LA level data (unrounded)'[Jobs])/1000.0)*1000,
ROUND(SUM('LA level data (unrounded)'[Jobs])/5000.0)*50000)
  • judspud's avatar
    judspud
    Solution Supplier
    6 years ago

    Hi Anonymous 

     

    The round function requires you to give it a number of digits to round to. 

     

    for example;

     

    ROUND(Value,0) would give you the value to the nearest integer.

     

    Hope this helps.

    George

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi George,

       

      Thanks,

      It was an error with the way I had written the formula, I think its fixed now! Thanks for your help!

  • JpTall's avatar
    JpTall
    Frequent Visitor
    6 years ago

    I often get this error when I have misplaced (or missed out) a bracket, and so it things you haven't given a 'then' or 'else' statement.

    Make sure you haven't missed a bracket after ROUND(SUM())