topic Re: ROUNDDOWN with a minimum of 1 in DAX Commands and Tips

ROUNDDOWN with a minimum of 1

Anonymous — Mon, 20 Mar 2023 17:55:09 GMT

Greetings, looking to round a formula down but I'm running in to an issue where values less than 0.5 return a 0. I'd like to set a minimum value of 1 in these instances.

I've tried an if/then statement where IF the formula returns a value equal to "0" then change it to "1" but the issue with this is that it messes with the grouping and returns a hundreds of blank values and repeats the groups hundreds of times.

Here's the forumula I'm using. Help is much appreciated.

10% Increase:=ROUNDDOWN([# New SS (LY)]*VALUES(Increase_Target[NEW SS increase:]),0)

Re: ROUNDDOWN with a minimum of 1

WinterMist — Mon, 20 Mar 2023 19:25:38 GMT

Anonymous

Could you provide a small dataset example & visual which show the issue mentioned?

ISSUE: "...it messes with the grouping and returns a hundreds of blank values and repeats the groups hundreds of times."

Regards,

Nathan

Re: ROUNDDOWN with a minimum of 1

Anonymous — Mon, 20 Mar 2023 20:10:53 GMT

If I ROUNDDOWN but don't set a minimum of "1"... I get each business unit properly mapped out in the hierarchy.

If I add an IF/THEN statement (if the rounddown formula is less than 1), it breaks the hierarchy and lists every child unit in the company under every parent unit with no value (just blanks).

Effectively, what I want is the first scenario but I want the "0's" to be "1's".

Re: ROUNDDOWN with a minimum of 1

WinterMist — Mon, 20 Mar 2023 20:26:05 GMT

Anonymous

My apologies. This is a bit more complex than I originally thought.

- How is the hierarchy defined? What are the parent, child, granchild items?

- Can you also show an example which displays the column names used, and the definitions of measures within your original measure "[10% Increase]"?

- Ah actually, this is a Calculated Column (and not a measure), correct?

Regards,

Nathan

Re: ROUNDDOWN with a minimum of 1

n8ball — Mon, 20 Mar 2023 20:31:39 GMT

Give this a try:

10% Increase = VAR new_ss = ROUNDDOWN ( [# New SS (LY)] * VALUES ( Increase_Target[NEW SS increase:] ), 0 ) RETURN IF (ISBLANK (new_ss), BLANK(), MAX ( new_ss, 1))

You expression is strange though. I assume this is a calculated column. VALUES returns a table of all unique values in the column referanced. I think you want to use something different there, like CALCULATE ( SUM(...
Maybe because of how your model is built you are getting lucky but this could be a source for breakage.