Forum Discussion
Comparing Value against a range and apply conditional formatting based on result
Hello,
I have a table of data across multiple months: Month, KPI, Actual, Goal
I want to show whether we're performing better or worse than target goal.
Below is a sample table I have as a PowerBI visual, the Acceptable? column is expected results
The ask is to show a green dot on rows where we're doing better than expected and red on rows where we're doing worse. Rows 1 and 3 would be green and row 2 is red.
| Month | KPI | Actual | Goal | Acceptable? |
| July | Retention (percent) | 82 | 80 | Yes |
| July | Average Wait Time (seconds) | 600 | 300 | No |
| July | Average Abandoned Calls (percent) | 2 | 5 | Yes |
All I could think of is to create a lower/upper bound measure and use an IF function to compare the Actual column to that.
Performance = IF([ActualMeasure]>=[UpperBound],"Good", IF([ActualMeasure]>=[LowerBound],"Good","Bad"))
However, this doesn't allow for varying good/bad scenarios (some KPI's are performing better if Actual <= Goal, while others are performing better if Actual >= Goal).
I know I need to start by identifying what would be considered an "Acceptable" range (+/- 5) and then specify for each KPI, what is considered good and bad
Example:
Retention is Actual < Goal (+/- 5) = Bad
Average Wait Time is Actual > Goal (+/-5) = Bad
Average Abandoned Calls is Actual > Goal (+/-5) = Bad
However, when I try to apply the "IF" measure above, the result is duplicating columns and not accurately marking them as good/bad. Unfortunately I can't access or upload an actual .pbix file due to computer restrictions from my employer.