Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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 ...
Anonymous's avatar
Anonymous
Not applicable
5 years ago

lbendlin based on your recommendation i used the formula below on my real data. It does not seem to be working for me and not sure why?

As you can see in the table India and Austrailia is less than 80% hence the KPI should be red/not met/false.


(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"))

lbendlin's avatar
lbendlin
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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.