Row value vs Average total conditional formatting

[Aditya-Upadhyay]

Hi All,

I have a simple table with value:

Col1  Col2

A       100

B       150

C       200

D      250

I want to highlight Col2 values which are less than the average of all the Col2 values(average is 175) as red and more than and equal to values as green. 100 and 150 will come as red and 200 and 250 will come as green. Is there a way I can acheive this?

[MFelix · Accepted solution]

Hi @Aditya-Upadhyay ,

Create the following measure:

Condittional formatting  = IF(SUM('Table'[Column2]) <= AVERAGEX(ALLSELECTED('Table'[Column1];'Table'[Column2]);'Table'[Column2]);"Red";"Green")

Then just use this column to make the condittional formatting:

You can change the "Red" "Green" to custom colour formatting using the HEX codes like this "#ffffff"

[Greg_Deckler]

@Aditya-Upadhyay - You can create a measure like:

Color Flag = 
  VAR __Average = AVERAGEX('Table',[Col2])
RETURN
  IF(MAX([Col2])<__Average,1,2)

You can use this measure in your conditional formatting rules. 1 is red, 2 is green. 

[amitchandak]

@Aditya-Upadhyay , One of the way is to condition formatting using measure with Option "Field Value"

Create a measure like this and choose it in conditional formatting after selecting "Field Value"

Color Date = if(FIRSTNONBLANK('Date'[Date],TODAY()) <today(),"lightgreen","red")

Color Date =
var _min =minx(allselected(Date,Date[Year])
return
 Switch( true(),
 FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"lightgreen",
  FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"blue",
 "red")

if(FIRSTNONBLANK(Table[Value],"true")= "true","green","red")

if(FIRSTNONBLANK(date[date],blank())= tofay(),"green","red")
if(max(date[date])= tofay(),"green","red")

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