ROUNDDOWN with a minimum of 1

[Anonymous]

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)

[n8ball]

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. 

[WinterMist]

@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

[WinterMist]

@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

[Anonymous]

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".