conditional formatting for monthly actuals based on monthly target where each row is a unique KPI

[cm008507]

Hi there,  I am new to PoweBI desktop and attempting to creating a table with different KPIs in column 1 with annual target nalue in column 2, and actual monthly performance values for the fiscal year in the subsequent columns. 

I'd like to have conditional formating for each month's actual values based on the annual target value for each KPI.  As each row contains a unique KPI, the formatting rules will be unique to each row.   Thresholds and table layout shown below.

	Red	Amber	Green/Best	Amber	Red
KPI1	< 80%	80% to 95%	> 95%
KPI2			<=0.20	0.20 to 0.25	>0.25
KPI3			<= 4.5	4.5 to 4.6	>4.6
KPI4			<= 4.5	4.5 to 4.6	>4.6
KPI5	< 60%	60% to 80%	80% to 100%	101% to 105%	>105%

KPI Name	2022 Target	Jan Actual	Feb Actual	Mar Actual
KPI1	$                9.69	$        9.83	$       10.15	$    14.25
KPI2	0.21	0.26	0.19	0.21
KPI3	4.00	4.55	4.60	4.70
KPI4	$            211.00	$    175.00	$     225.00	$  210.00

What is the best way to do this?   Thank-you in advance.

[v-easonf-msft · Accepted solution]

Hi, @cm008507 

I'm afraid there is currently no other better way, you have to apply conditional formatting column by column.

Example (conditional formating for column 'Jan actual')

formatting_Jan = 
SWITCH (
    MAX ( 'Fact Table'[KPI Name] ),
    "KPI1",SWITCH (TRUE (),[Jan_Actualvalue] < 0.8, "Red",[Jan_Actualvalue] <= 0.95, "#ffbf00",[Jan_Actualvalue] > 0.95, "light Green"),
    "KPI2",SWITCH (TRUE (),[Jan_Actualvalue] <= 0.20, "light Green",[Jan_Actualvalue] <= 0.25,"#ffbf00",[Jan_Actualvalue] > 0.25, "Red"),
    "KPI3",SWITCH (TRUE (),[Jan_Actualvalue] <= 4.5, "light Green",[Jan_Actualvalue] <= 4.6, "#ffbf00", [Jan_Actualvalue] > 4.6, "Red" ),
    "KPI4",SWITCH (TRUE (),[Jan_Actualvalue] <= 4.5, "light Green", [Jan_Actualvalue] <= 4.6, "#ffbf00",[Jan_Actualvalue] > 4.6, "Red"),
    "KPI5",SWITCH (TRUE (), [Jan_Actualvalue] < 0.6, "Red",[Jan_Actualvalue] <= 0.8, "#ffbf00",[Jan_Actualvalue] <= 1, "light Green",[Jan_Actualvalue] <= 1.05, "#ffbf00",[Jan_Actualvalue] > 1.05,"Red")
)

Best Regards,
Community Support Team _ Eason

[cm008507]

Thank-you for your assistance.