Change background colour for table data

[lee-martin]

I've got a number of tables, with various results in the table.

ie yes, no, not yet, not necessary

I need to assign a colour to each (ie green for yes, red for no, amber for not yet, yellow for not necessary).

When I try and assign background colours, it's based on a count and I can only do this on numeric values. My columns are from a SharePoint List and are Choice fields. How can I assign a background colour based on these results?

Thanks

[v-eqin-msft · Accepted solution]

Hi @lee-martin ,

1.You could use the following formula to create a measure and the apply it to Conditional Formatting:

Color Measure = SWITCH(MAX('Table'[Column1]),"yes","Green","no","Red","not yet","#FFBF00","not necessary","Yellow")

2. Go to Format pane-->Disable "Total" to not show total field: 

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

[amitchandak]

@lee-martin , You can create a color measure with column or measure or with both. And use them in conditional formatting using field value option

Color Year = if(FIRSTNONBLANK('Table'[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK('Table'[Year],2014)>2018,"red","yellow"))

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

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[lee-martin]

I've now managed to do this with a column - however it's added a row for "total" at the bottom which I really don't need. Is that easy to remove? (it's not easy to google it)

[lee-martin]

Thanks, there's no way of doing through the interface like there is for numbers?