Nested If Statement using measures

[Anonymous]

Hi all,

I'm trying to do a dynamic calculation based on a target staff and actual staff. The calculation would be either "actual staff / target staff" or "target staff / actual staff". Basically I want which ever one is lowest to be the numerator and highest to be denominator.

I'm using a slicer to do what if analysis on staff target and actual to see how efficiency could change.  

Trying to figure out this in dax calculation:

If target staff > actuall staff then actual / target

Else if target staff < actual staff then target / actual

Else if target staff = actual staff then target / actual

Formula I am trying but getting an error:

IFMeasure = IF(max('Staff Target'[Staff Target] > 'Staff Actual'[Staff Actual Value], 'Staff Actual'[Staff Actual Value divided by Staff Target Value 2]))

Cheers

[Anonymous · Accepted solution]

@Anonymous ou are missing closing paranthesis in you syntax. You can use below code

Measure = 
VAR actual = MAXX('Table','Table'[actual staff])
VAR target = MAXX('Table','Table'[target staff])
RETURN IF(target>actual,DIVIDE(actual,target,0),DIVIDE(target,actual,0))

[Anonymous]

Thank you @Anonymous. This worked perfectly!