Forum Discussion
Anonymous
5 years agoNot applicable
Conditional Formatting based on 2 dimensions
Hi, Below is how my data is structured. I have an KPI and for the KPI to be "Met" 2 it shoud meet the 2 conditions below. The conditions are as below: Condition 1: At Global level the score should ...
lbendlin
5 years agoSuper User
Your measure looks like this
Kpi 01 Color =
IF (
AND (
AVERAGEX (
'Sla+Contract',
IF (
SUM ( 'Sla+Contract'[SLA] ) / SUM ( 'Sla+Contract'[Scope KPI 1] )
= BLANK (),
"N/A",
SUM ( 'Sla+Contract'[SLA] ) / SUM ( 'Sla+Contract'[Scope KPI 1] )
)
) >= 0.95,
MINX (
'Sla+Contract',
IF (
SUM ( 'Sla+Contract'[SLA] ) / SUM ( 'Sla+Contract'[Scope KPI 1] )
= BLANK (),
"N/A",
SUM ( 'Sla+Contract'[SLA] ) / SUM ( 'Sla+Contract'[Scope KPI 1] )
)
) >= 0.8
),
"#009900",
"#ff2312"
)
That is a bit complex. Let's rewrite it.
Kpi 01 Color =
IF (
AVERAGEX (
'Sla+Contract',
DIVIDE (
SUM ( 'Sla+Contract'[SLA] ) , SUM ( 'Sla+Contract'[Scope KPI 1] )
,"N/A"
)
) >= 0.95 &&
MINX (
'Sla+Contract',
DIVIDE (
SUM ( 'Sla+Contract'[SLA] ) , SUM ( 'Sla+Contract'[Scope KPI 1] )
,"N/A"
)
) >= 0.8,
"#009900",
"#ff2312"
)
Bit better but still not good. First of all you are comparing "N/A" to a number. Then you are mixing an aggregator function MINX with a scalar function SUM. That may or may not work (average of sums vs sum of averages pitfall).
Break the issue down into smaller parts, use lots of variables and validate that these variables return the expected intermediate results.
Anonymous
5 years agoNot applicable
lbendlin i removed the "N/A" from the formula and tried, however it did not work. What am i doing wrong here ? i know its not straightforward but any help would be appreciated to set the context for both conditions.