topic Re: Conditional Formatting based on 2 dimensions in DAX Commands and Tips

Conditional Formatting based on 2 dimensions

Anonymous — Mon, 14 Sep 2020 23:49:01 GMT

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%

Re: Conditional Formatting based on 2 dimensions

lbendlin — Tue, 15 Sep 2020 00:11:51 GMT

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

Re: Conditional Formatting based on 2 dimensions

amitchandak — Tue, 15 Sep 2020 01:44:39 GMT

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

Re: Conditional Formatting based on 2 dimensions

Anonymous — Tue, 15 Sep 2020 20:39:43 GMT

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

Re: Conditional Formatting based on 2 dimensions

lbendlin — Tue, 15 Sep 2020 22:04:16 GMT

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.

Re: Conditional Formatting based on 2 dimensions

Anonymous — Thu, 17 Sep 2020 19:40:00 GMT

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.