Create Indicator Color/Light With A Single Column

[Anonymous]

Hi, 

I need to create indicator from one column called "FIELD". Basically, the column has two string values "EOD" and "SOD". The indicator that i need to create is: 

If SOD > EOD then red color 

IF EOD < SOD then green color 

IF SOD = EOD then yellow color

Could anyone please help with this.  Thank you in advance. 

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a sample to test:

Then try these measures:

Color 1 = 
var EOD_1 = CALCULATE(MAX('Table'[Value1]),FILTER('Table','Table'[Field]="EOD"))
var SOD_1 = CALCULATE(MAX('Table'[Value1]),FILTER('Table','Table'[Field]="SOD"))
return
SWITCH(true,EOD_1>SOD_1,"#FF0000",EOD_1<SOD_1,"#00FF00",EOD_1=SOD_1,"#FFFF66")

Color 2 = 
var EOD_2 = CALCULATE(MAX('Table'[Value2]),FILTER('Table','Table'[Field]="EOD"))
var SOD_2 = CALCULATE(MAX('Table'[Value2]),FILTER('Table','Table'[Field]="SOD"))
return
SWITCH(true,EOD_2>SOD_2,"#FF0000",EOD_2<SOD_2,"#00FF00",EOD_2=SOD_2,"#FFFF66")

Color 3 = 
var EOD_3 = CALCULATE(MAX('Table'[Value3]),FILTER('Table','Table'[Field]="EOD"))
var SOD_3 = CALCULATE(MAX('Table'[Value3]),FILTER('Table','Table'[Field]="SOD"))
return
SWITCH(true,EOD_3>SOD_3,"#FF0000",EOD_3<SOD_3,"#00FF00",EOD_3=SOD_3,"#FFFF66")

Apply these measures to three value columns' background conditional formatting, the result shows:

Hope this can help.

Best Reagrds,

Giotto Zhi

[amitchandak]

You can create a measure like this

Color Category = SWITCH(true(),
Max(Sheet1[Category]) in {"A"} && Sheet1[Target]<10,"green",
Max(Sheet1[Category]) in {"B"} && Sheet1[Target]<10,"orange"
"red"
)

You have to aggregation for the measure. Then you can use this under advance control fields.

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values