Forum Discussion
Helper IEvaluate for Every Row
Hello,
I have data as per the below table. I also have a matrix visual for the data per the snippet below.
I have a measure for conditional formatting the background colours (current code below). Instances where KPI:Favourable Trend is "L" a lower KPI Value vs. Target is positive ("Green"); higher KPI Value vs. Target is negative ("Red"). On the other hand, where KPI:Favourable Trend is "H" a lower KPI Value vs. Target is negative ("Red"); higher KPI Value vs. Target is positive ("Green").
The issue I'm having is that the conditional format evaluates the KPI Value vs. Target. The target value can change during the year, in which case, the format color should change appropriately. Right now my measure evaluates for the sum.
How can I code my measure so that it evaluates the KPI Value vs. Target for every row (i.e. Month)?
| ID | KPI:Title | Year | Month | KPI:Favourable Trend | Target | KPI Value |
| 151 | Station Ambassador Costs per rider | 2022/23 | Apr | L | 0.69 | 0.75 |
| 152 | Station Ambassador Costs per rider | 2022/23 | May | L | 0.69 | 0.62 |
| 153 | Station Ambassador Costs per rider | 2022/23 | Jun | L | 0.69 | 0.52 |
| 154 | Station Ambassador Costs per rider | 2022/23 | Jul | L | 0.69 | 0.51 |
| 155 | Station Ambassador Costs per rider | 2022/23 | Aug | L | 0.69 | 0.5 |
| 156 | Station Ambassador Costs per rider | 2022/23 | Sep | L | 0.69 | 0.5 |
| 157 | Station Ambassador Costs per rider | 2022/23 | Oct | L | 0.69 | 0.48 |
| 158 | Station Ambassador Costs per rider | 2022/23 | Nov | L | 0.69 | 0.45 |
| 159 | Station Ambassador Costs per rider | 2022/23 | Dec | L | 0.69 | 0.69 |
| 160 | Station Ambassador Costs per rider | 2022/23 | Jan | L | 0.56 | 0.6 |
| 247 | Station Ambassador Costs per rider | 2022/23 | Feb | L | 0.55 | 0.57 |
| 430 | Station Ambassador Costs per rider | 2023/24 | May | L | 0.38 | 0.51 |
Expected output:
Red - Jan, Feb, Apr
Green - May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
KPI Cond. Formatting Color =
SWITCH (
TRUE (),
CALCULATE (
SUM ( 'CSD KPIs Data'[KPI Value] ) >= SUM ( 'CSD KPIs Data'[Target] ),
'CSD KPIs'[Favourable Trend] = "H"
), "#73b761",
CALCULATE (
SUM ( 'CSD KPIs Data'[KPI Value] ) < SUM ( 'CSD KPIs Data'[Target] ),
'CSD KPIs'[Favourable Trend] = "H"
), "#fd625e",
CALCULATE (
SUM ( 'CSD KPIs Data'[KPI Value] ) > SUM ( 'CSD KPIs Data'[Target] ),
'CSD KPIs'[Favourable Trend] = "L"
), "#fd625e",
CALCULATE (
SUM ( 'CSD KPIs Data'[KPI Value] ) <= SUM ( 'CSD KPIs Data'[Target] ),
'CSD KPIs'[Favourable Trend] = "L"
), "#73b761"
)
Instead of CALCULATE you can do a simple &&
KPI Cond. Formatting Color = SWITCH ( TRUE (), SUM ( 'CSD KPIs Data'[KPI Value] ) >= SUM ( 'CSD KPIs Data'[Target] ) && 'CSD KPIs'[Favourable Trend] = "H" , "#73b761", SUM ( 'CSD KPIs Data'[KPI Value] ) < SUM ( 'CSD KPIs Data'[Target] ) && 'CSD KPIs'[Favourable Trend] = "H" , "#fd625e", SUM ( 'CSD KPIs Data'[KPI Value] ) > SUM ( 'CSD KPIs Data'[Target] ) && 'CSD KPIs'[Favourable Trend] = "L" , "#fd625e", SUM ( 'CSD KPIs Data'[KPI Value] ) <= SUM ( 'CSD KPIs Data'[Target] ) && 'CSD KPIs'[Favourable Trend] = "L" , "#73b761" )
1 Reply
- lbendlin
Super User
Instead of CALCULATE you can do a simple &&
KPI Cond. Formatting Color = SWITCH ( TRUE (), SUM ( 'CSD KPIs Data'[KPI Value] ) >= SUM ( 'CSD KPIs Data'[Target] ) && 'CSD KPIs'[Favourable Trend] = "H" , "#73b761", SUM ( 'CSD KPIs Data'[KPI Value] ) < SUM ( 'CSD KPIs Data'[Target] ) && 'CSD KPIs'[Favourable Trend] = "H" , "#fd625e", SUM ( 'CSD KPIs Data'[KPI Value] ) > SUM ( 'CSD KPIs Data'[Target] ) && 'CSD KPIs'[Favourable Trend] = "L" , "#fd625e", SUM ( 'CSD KPIs Data'[KPI Value] ) <= SUM ( 'CSD KPIs Data'[Target] ) && 'CSD KPIs'[Favourable Trend] = "L" , "#73b761" )