Conditional formatting with measure on criteria

[yuhkao]

Hi Expert

Based on this question, I got deeper equation on data and would like to apply conditional format with color code on different threshold.

example:

                            Jan-2020  Feb-2020  Mar-2020  Apr-2020

commodity1         data         data          data          data

commodity2         data         data          data          data

commodity3         data         data          data          data

Data threhold1 =  initial goal + 1 *2.57* SQRT(initial goal*(1- initial goal)/base)

Data threhold2 =  initial goal + 2 *2.57* SQRT(initial goal*(1- initial goal)/base)

Data threhold3 =  initial goal + 3 *2.57* SQRT(initial goal*(1- initial goal)/base)

*initial goal is different upon commodity

*base is differnt upon month usage or commodity

With data threhold1 to 3, would like to apply different color code, like green, yellow, red

thanks 

[amitchandak]

@yuhkao , You can create a measure like this. And used it with Field option in conditional formatting

Color  = if(FIRSTNONBLANK(Table[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170 
,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))
Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
/// Add more conditions
"red"
)

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
https://community.powerbi.com/t5/Desktop/FORMAT-icon-set-for-use-in-a-data-card/td-p/811692