Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not 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 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 5
amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
lbendlin
Super User
Super User

 

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

 

 

lbendlin_0-1600128597074.png

lbendlin_0-1600128702845.png

 

lbendlin_1-1600128631622.png

 

Anonymous
Not applicable

@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"))Color.png

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

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.