Power BI conditional formatting - matrix and multiple conditions

Question

I have this issue in Power BI I can't figure out. I have a sharepoint excel sheet (I can't edit) loaded in PBI desktop. It's a matrix/table, with projects, their SLAs and weekly actual values. The values need to be colour coded for each week and value, based on if we hit the SLA target or not that week. But some values are "lower the better", some are "higher the better".

How the matrix looks like in PBI

Also there are 53 week columns and I can't figure out how to apply conditional formatting on all these in style as picture shows. Thank you so much for any help!

I tried applying conditional formatting on columns, but, there are too many columns and also it allows me to either apply "lower the better" or "higher the better" logic. There must be a better way

Project	SLA	Target	Unit	W1	W2	W3	W4	W5	.....	W53
Amazon	Calls answered	80	%	90	70	85
Amazon	Sales closed	70	%	75	80	90
Amazon	Sick hours*	465	h	200	560	540
Aldi	Calls answered	80	%	70	89	90
Aldi	Emails answered	90	%	80	81	82
Aldi	Avg time to handle*	300	sec	360	205	200
Tesco	Sales closed	70	%	50	76	79
Tesco	Emails answered	90	%	90	91	93
Tesco	Chats answered	90	%	95	94	95
Tesco	Avg time to handle calls*	350	sec	360	460	340

Conditional formatting

Measure error

Answer 1

Ideally, for your Matrix, you would have one measure for the Values, and another for Conditional Formatting. You would also have a column for W1...W53 that you would use in the Matrix Columns.

For the above, you will need to re-shape your data model. In PowerQuery I would split your one table into two tables.

1. Add an Index column to your table - this will be used as a "Metric Id" so feel free to rename that column.
2. Reference that query, into a new query and drop all the Wx columns. Name the query SLA.
3. Reference query in 1 again, into a new query, drop all columns except the Wx columns and the Index/ID column. Then right-click on the Metric Id header and select Unpivot other columns. In the Formula bar, rename Attribute to Week. Name the query SLA Value.
4. Right-click on the query in step 1, and de-select enable load.
5. Create a new Blank Query with the following. Name the query Week:

let
  Source = List.Generate(() => 1, each _ < 54, each _ + 1, each "W" & Text.From(_)),
  #"Converted to table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Renamed columns" = Table.RenameColumns(#"Converted to table", {{"Column1", "Week"}})
in
  #"Renamed columns"

You should hopefully have three tables looking like this:

Table/Query: SLA (from step 2 above)

Project	SLA	Target	Unit	Metric Id

Table/Query: SLA Value (from step 3 above)

Metric Id	Week	Value
1	W1	90
1	W2	70

Table/Query: Week (from step 5 above)

Week
W1
W2

Close & Apply.

Create relationships so your model looks like:

Essentially, SLA Value is your Fact table, and the other two are your Dimension tables.

Create a measure that we'll use for the Conditional Formatting:

CF SLA Value = 
  var t = MIN('SLA'[Target])
  var v = MIN('SLA Value'[Value])
  var lowerBetter = CONTAINSSTRING(SELECTEDVALUE('SLA'[SLA]), "~*")
  return IF(
    lowerBetter,
    IF(v <= t, "G", "R"),
    IF(v >= t, "G", "R")
  )

Construct the Matrix:

• Rows: from the SLA table, add: Project, SLA, Unit, Target
• Columns: from the Week table, add: Week
• Values: from the SLA Value table, add: Value

Then expand all rows.
In the visual properties, under Row headers:

• Switch off +/- icons
• Switch off Stepped layout

And switch off Column subtotals and Row subtotals.

Under Cell elements, add Background color Conditional Formatting with the following:

Should hopefully give you a matrix like this:

---

The other alternative without changing your one table or data model, is to create 53 measures, one for each column, that will be used for the Conditional Formatting rule. And then applying these 53 times.