Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 ?
Month | Region | Country | City | Score |
Jan-20 | North America | United States | Log Angeles | 80% |
Jan-20 | North America | United States | New Jersey | 70% |
Jan-20 | North America | Canada | Vancouver | 100% |
Jan-20 | North America | Canada | Toronto | 85% |
Jan-20 | Europe | France | Leon | 90% |
Jan-20 | Europe | Switzerland | Zurich | 95% |
Jan-20 | Europe | Germany | Berlin | 100% |
Jan-20 | Europe | Italy | Naples | 80% |
@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
KPI = AVERAGEX(Scores,Scores[Score])>=0.8 && MINX(Scores,Scores[Score])>=0.8
@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"))
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.
@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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |