cancel
Showing results for
Did you mean:
New Member

## Row value vs Average total conditional formatting

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?

1 ACCEPTED SOLUTION Super User III

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"

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português 3 REPLIES 3 Super User III

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"

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog:

Power BI em Português  Super User IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User! Super User IV

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