Row Level Conditional Formatting on Table

[bitofanewb]

Hi,

I've got a table I created using DAX that I need to apply a RAG rating to for the RAG Status rows, which I've left blank as the columns are data ones:

The RAG status is mostly based on the % Variance to Budget ( >= 0, "Green", >= -10, "Yellow", < -10, "Red").

Is there a way I can create a measure to target only the rows which have a KPI of "RAG Status", and them specifically for the RAG rating that needs applied (Area = "Total" & KPI Area = "Starts", Area = "AU Total" & KPI Area = "FiL" etc) and for which column (YtoD Avg, YtoD Total, Current Month)? I've tried doing it a lot of ways, but no matter how I try, it seems to target the correct rows, but won't apply the RAG rating, showing everything as "Green" as that's the first colour mentioned.

If not, I have created a separate RAG Status table (using the same new query table & DAX method as above) which I can overlay with the individual table visualisations, but this is fiddly and I'd prefer to do it using DAX if at all possible.

Many thanks for looking, and any advice you can offer.

[danextian]

Hi @bitofanewb 

The sample measure should be able specifically target just the RAG Status rows but needs to be applied to each column individually (row-wide conditional formatting on a table for all columns isn't currently supported).

IF (
    SELECTEDVALUE ( 'tbl'[KPI] ) = "RAG Status",
    "conditional formatting color"
)

Unless you mean something else...

[bitofanewb]

Hi,

Thanks for the quick reply.

Apologies, I thought I'd sent my response.

Unfortunately that doesn't work as I need the conditional formatting colour to match the RAG status figure (>= 0 is "Green", >=-10 is "Yellow", <-10 is "Red"). When I try to use IFs or SWITCH, it only looks at the first statement and first colour, so doesn't actually work.

I know I'll need to repeat the formula for the 3 columns, I just need to find a way to get it right for one, then can repeat for the other 2.

Annoyingly it looks like layering the visualisations isn't an option either, due to the fact you can't remove the headers, or make their text or background no colour. 

[danextian]

Hi @bitofanewb 

The sample formula is based on what I currently which or may not work on the actual pbix. Please provide sanitized copy of your pbix (confidential data removed) if you want to proceed.  You may post  a link to a file stored in the cloud.

[bitofanewb · Accepted solution]

Hi,

Unfortuately it would've taken a long time to sanitise the file.

Thanks for your help and quick responses, they were very much apprecaited.

I managed to do what I needed to do, by creating columns for the RAG for each month, creating a YtoD Avg, YtoD Total & Current Month for them, and formatting the data in those 3 columns as text in new ones. Then referrencing those columns in the YtoD Avg, YtoD Total & Current Month columns for when [KPI] = "RAG", otherwise using the normal month columns to do the necessary calculations for the other [KPI] values. There was a more complex RAG status (not the same as the other) which was a lot of work beyond even that, but I've managed that too. It was just a case of more and more columns, and converting numbers to text, to get to what I needed.