Forum Discussion

BI_Newbie2's avatar
BI_Newbie2
Regular Visitor
1 year ago
Solved

Help! Measure Not Working Correctly

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.
 
Note:  this is the first time trying to share a file, so I'm hoping this link will work! 
Need Help
  • amitchandak's avatar
    amitchandak
    1 year ago

    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's avatar
    Anonymous
    1 year ago

    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!

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    1 year ago

    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's avatar
    Anonymous
    Not applicable
    1 year ago

    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!