Forum Discussion
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 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
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
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())
5 Replies
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
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)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