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 be >=80%,

Condition 2:  No country should be less than 80%.  

 

As per the conditions above the KPI for the month of Jan will be "Not Met" because even though Global score is 87.50% but United States is at 75%.
Can someone tell me how can i do this ?

 

MonthRegionCountryCityScore
Jan-20North AmericaUnited StatesLog Angeles80%
Jan-20North AmericaUnited StatesNew Jersey70%
Jan-20North AmericaCanadaVancouver100%
Jan-20North AmericaCanadaToronto85%
Jan-20EuropeFranceLeon90%
Jan-20EuropeSwitzerlandZurich95%
Jan-20EuropeGermanyBerlin100%
Jan-20EuropeItalyNaples80%



5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

     

    KPI = AVERAGEX(Scores,Scores[Score])>=0.8 && MINX(Scores,Scores[Score])>=0.8

     

     

     

     

    • 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.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Create a color measure

     

    color = if([Score]<= 80 ,"red", "white")

     

    And you have to do it for all columns. Go to conditional formatting for each column choose font formatting and under that use "field values" and select this color measure

     

    https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
    https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values