Help! Measure Not Working Correctly

[BI_Newbie2]

Hi all,

I'm fairly new to BI and I am trying to create a column that I can use as a Conditional Formatting Icon column.  It's fairly simple, or at least I thought it was, I'm trying to create a measure in my Fact table to apply a "Good or Bad" scale to identify if the current state is in "Good" shape or "Bad" shape, if the actual value is within 5 pts of the forecasted value it's still considered "Good"

VAR HealthInd = SELECTEDVALUE(DIM_KPI[HealthInd]) VAR Actual = SELECTEDVALUE('FACT'[Actual]) VAR Up = SELECTEDVALUE('FACT'[Upper]) VAR Low = SELECTEDVALUE('FACT'[Lower]) RETURN IF(HealthInd= "High" && Actual > Up || HealthInd = "Low" && Actual < Low, "Bad", "Good")

However when I add the new measure to my table, I keep getting results that are multiplied several times and not even accurately.  I need to be able to filter by month and have 1 row for each KPI but it just won't work and I'm not sure what I'm doing wrong.

Example.pbix 

Note:  this is the first time trying to share a file, so I'm hoping this link will work! 

[amitchandak · Accepted solution]

@BI_Newbie2 , Try measure with this modification

Health Status =
VAR HealthInd = MAX(DIM_KPI[HealthInd])
VAR Actual = MAX('FACT'[Actual])
VAR Up = MAX('FACT'[Upper])
VAR Low = MAX('FACT'[Lower])
RETURN
IF(
HealthInd = "High" && Actual > Up ||
HealthInd = "Low" && Actual < Low,
"Bad",
"Good"
)

[Anonymous · Accepted solution]

Thanks for the reply from amitchandak.

Hi @BI_Newbie2 ,

Is your aim at this stage to get the result that HealthInd is neutral with the “Good” status, if I understand you correctly, I rewrote your arithmetic logic:

NewHealthStatus =
VAR _act =
    SUM ( 'FACT'[Actual] )
VAR _up =
    SUM ( 'FACT'[Upper] )
VAR _down =
    SUM ( 'FACT'[Lower] )
VAR _health =
    MAX ( 'DIM_KPI'[HealthInd] )
RETURN
    IF (
        _act >= _down
            && _act <= _up
            && NOT _health IN { "Low", "High" },
        "Good",
        "Bad"
    )

The result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[amitchandak · Accepted solution]

@BI_Newbie2 , Try measure with this modification

Health Status =
VAR HealthInd = MAX(DIM_KPI[HealthInd])
VAR Actual = MAX('FACT'[Actual])
VAR Up = MAX('FACT'[Upper])
VAR Low = MAX('FACT'[Lower])
RETURN
IF(
HealthInd = "High" && Actual > Up ||
HealthInd = "Low" && Actual < Low,
"Bad",
"Good"
)