Help with Conditional Formatting

[leerjones85]

Afternoon all,

I am pretty new to Power BI and have come a bit stuck when trying to format the text colour on columns in my report table.

Base Table = FACT_METRICS

Target colour is based on the metric definition (can be different for different metrics)

METRIC	DATE	ACTUAL	TARGET	TARGET_MET
METRIC 1	22/10/2024	12	12	Purple
METRIC 1	21/10/2024	8.2	12	Green
METRIC 1	20/10/2024	12.5	12	Red
METRIC 2	22/10/2024	6.8	6.5	Green
METRIC 2	21/10/2024	6	6.5	Red
METRIC 2	20/10/2024	7	6.5	Green
METRIC 3	22/10/2024	6	4	Red
METRIC 3	21/10/2024	3	4	Green
METRIC 3	20/10/2024	4	4	Green
etc...	etc...	etc...	etc...	etc...

Report Table looks like... (Yesterday = 22/10/2024)

METRIC	YESTERDAY	DAY -2	DAY -3
METRIC 1	12	8.2	12.5
METRIC 2	6.8	6	7
METRIC 3	6	3	4
etc...	etc...	etc...	etc...

I want to conditional format Yesterday, Day-2 and Day -3 font colour to equal the colour in the TARGET_MET column.

If I try to add condiitonal formatting to any of these columns based on rule TARGET_MET it looks for the First or Last so isnt returning the correct colour.

Any help is much appreciated!

Thank You.

[SamWiseOwl · Accepted solution]

Hi @leerjones85 

You could create a measure for each defining the colour to return.

The code will be similar to your yesterday or -3day code.

Assign this as your Field value under the Background colour.

3day  Swap colour =

var day3 = CALCULATE(sum('Target Table'[ACTUAL]), 'Target Table'[DATE] = TODAY() -3)

RETURN

If(

   day3 >= CALCULATE(sum('Target Table'[TARGET]), 'Target Table'[DATE] = TODAY() -3)

    ,CALCULATE(SELECTEDVALUE('Target Table'[TARGET_MET]), 'Target Table'[DATE] = TODAY() -3)

)

[leerjones85]

Thank You! 🙂