Rounding - If statement

[Anonymous]

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 statement written below; however I have not been able to work out how to do this in PBI and it keeps returning errors, has anyone got any ideas? Thanks in advance!

IF SUM([Jobs]) < 10 then SUM([Jobs])

ELSEIF SUM([Jobs]) <15 then round(SUM([Jobs]) /10.0) *10

ELSEIF SUM([Jobs]) <500 then round(SUM([Jobs]) /25.0) *25

ELSEIF SUM([Jobs]) <1000 then round(SUM([Jobs]) /50.0) *50

ELSEIF SUM([Jobs]) <10000 then round(SUM([Jobs]) /100.0) *100

ELSEIF SUM([Jobs]) <25000 then round(SUM([Jobs]) /500.0) *500

ELSEIF SUM([Jobs]) <250000 then round(SUM([Jobs]) /1000.0) *1000

ELSE round(SUM([Jobs]) /5000.0) * 5000 END

[judspud · Accepted solution]

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

[judspud]

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]

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)

[JpTall · Accepted solution]

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 · Accepted solution]

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]

Hi George,

Thanks,

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