matrixpowerbipowerquerydashboard

Power BI conditional formatting - matrix and multiple conditions

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

Solution

  • 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: enter image description here

    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:

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

    And switch off Column subtotals and Row subtotals.

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

    enter image description here

    Should hopefully give you a matrix like this:

    enter image description here


    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.