Conditional formatting question

[vjnvinod]

Dear experts,

this is the visual I have created in power BI

 and P7W4 is a measure 

P7W4 = CALCULATE( DIVIDE(SUM('WTD FY22'[CH]),SUM('WTD FY22'[EAH]),"n/a"),'WTD FY22'[Period Week]="P7WK4")

I need to conditionally put some background color based on the below table rule

 if with the combination of SSL, Rank, Lower and Upper limit

P7W4 <= Lower Limit, then Red background, if P7W4="n/a", then grey color background and if P7W4> Lower Limit, Green background

But I am not sure, how to achieve this on the Power BI desktop

SSL	Rank	Target Utilization	Lower Limit	Upper Limit
ITTS	Senior	75%	65%	85%

[Anonymous · Accepted solution]

Hi @vjnvinod 

Create a measure to return the color value .

Measure = Switch( True() ,
[P7W4] =BLANK(),"Grey",
[P7W4] <= SELECTEDVALUE('Table'[Lower Limit]) , "red" ,
[P7W4] > SELECTEDVALUE('Table'[Lower Limit]) , "Green" )

Then set conditional formatting for [P7W4] in table visual . Choose Field value for Format style , Measure for What field should we base this on .

 

The final result is as shown :

I have attached my pbix file , you can refer to it .

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mh2587]

color measure =

Switch( True() ,

[P7W4] < [Lower] , "red" ,

[P7W4] > [Upper] , "Green" ,

"Grey"

)

[amitchandak]

@vjnvinod , That "n/a", might convert it to text

But you can create a measure like

Switch( True() ,

[P7W4] < [Lower] , "red" ,

[P7W4] > [Upper] , "Green" ,

"Yellow"

)

and you can use that in conditional formatting using field value option