Conditional Formatting with % and criteria

[ashicool_10061]

Hi There,

I am having asn issue while creating conditional formatting.

I have one column which is Service Level (%) and having values in % along with that i have criteria based on LOB'S present in my data.

I am struggling to apply conditional formatting . Can anyone help me in acheving below requirement :

So I need Conditional formatting in Serivce Level columns based on Conditions given .

Request to please help me in achieving this .

Regards,

Ash

@amitchandak @Ashish_Mathur 

[SamWiseOwl · Accepted solution]

Hi @ashicool_10061 

Create a measure in your table and use that for formatting:

Colour measure =

var ser = sum('Table'[Service level]) --if this is a measure just put the measure name

var lob = selectedValue('Table'[LOBs])

RETURN

Switch(

True()

, ser > 0.7 || ser > 0.55 && lob IN {"A","B"} || lob = "D" && ser > 60

,"Green"

, ser > 0.6 || ser > 0.50 && lob IN {"A","B"} || lob = "D" && ser > 50

,"Yellow"

, ser > 0.5 || ser > 0.45 && lob IN {"A","B"} || lob = "D" && ser > 40

,"Red"

,"Blue")

Then assign this to your field formatting:

[SamWiseOwl · Accepted solution]

Hi @ashicool_10061 

Create a measure in your table and use that for formatting:

Colour measure =

var ser = sum('Table'[Service level]) --if this is a measure just put the measure name

var lob = selectedValue('Table'[LOBs])

RETURN

Switch(

True()

, ser > 0.7 || ser > 0.55 && lob IN {"A","B"} || lob = "D" && ser > 60

,"Green"

, ser > 0.6 || ser > 0.50 && lob IN {"A","B"} || lob = "D" && ser > 50

,"Yellow"

, ser > 0.5 || ser > 0.45 && lob IN {"A","B"} || lob = "D" && ser > 40

,"Red"

,"Blue")

Then assign this to your field formatting:

[ashicool_10061]

Hi @SamWiseOwl 

I also need to add two more conditions which is in different columns .
Can you tell what should i modify in this?

Thanks,

Ashish

[SamWiseOwl]

Colour measure =

var ser = sum('Table'[Service level]) --if this is a measure just put the measure name

var lob = selectedValue('Table'[LOBs])

var si = selectedValue('Table'[Si's])

RETURN

Switch(

True()

, ser > 0.7 || ser > 0.55 && (lob IN {"A","B"} || si IN {"a(i)","b(i)"})  || lob = "D" && ser > 60

,"Green"

, ser > 0.6 || ser > 0.50 && (lob IN {"A","B"} || si IN {"a(i)","b(i)"})  || lob = "D" && ser > 50

,"Yellow"

, ser > 0.5 || ser > 0.45 && (lob IN {"A","B"} || si IN {"a(i)","b(i)"})  || lob = "D" && ser > 40

,"Red"

,"Blue")

[ashicool_10061]

Thank you @SamWiseOwl 
Just forget to add D aslo has SI level d(i) and d(II) . So can i add  like this ;

ser > 0.7 || ser > 0.55 && (lob IN {"A","B"} || si IN {"a(i)","a(i)"})  || lob = "D" && si in {"d(I)",d(ii)}  && ser > 60

,"Green"

and SO on ??

[SamWiseOwl · Accepted solution]

(lob = "D" || si in {"d(I)",d(ii)})  && ser > 60

Need the brackets so they happen at the same time.

Assuming its Lob = D OR Si = "d(i)" if it is AND use &&

[ashicool_10061]

Hi @SamWiseOwl ,

That was really quick. Thank you for this . I appreicate it .

Thanks,

Ashish

[Shivu-2000]

Hi @ashicool_10061 

These might be helpful.

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

https://www.youtube.com/watch?v=m0h3Ghl6mgY

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

[dk_dk]

Hi @ashicool_10061 ,

You could do this by creating a new calculated column that captures your criteria ( using SWITCH and IF statements) and returns the corresponding hex color code. Then you can use this calculated column to drive the conditional formatting of the service level column.

I hope this helps!