Row wise conditional formatting in Matrix Power bi

[VamshiGoud]

Hello Friends,

I have a requirement where i need to show row wise conditional background color for percentage in Matrix table.

For instance : 

I have a product column which contains A,B,C and it has month wise percentage values. Now my requirement is need to show 3 diffrent colours based on 3 criterias for each category,

For Product A if value lessthen 70% it should get Red, 80% Amber and greatehrthan 90% green

For Product B if value lessthen 60% it should get Red, 70% Amber and greatehrthan 80% green

For Product C if value lessthen 50% it should get Red, 60% Amber and greatehrthan 70% green

If anyone knows the answer please let me know. 

Thanks in Advance

[v-bofeng-msft · Accepted solution]

Hi @VamshiGoud ,

Thanks for @audreygerred's solution, that's greate. Since Power BI does not support conditional formatting for rows , as suggested by @audreygerred , you may choose between two options: 'using a measure to apply conditional filtering for background color' or 'transposing the table and then creating conditional formatting on the new columns'. Please allow me to add some specific steps to this，

Option1:using a measure to apply conditional filtering for background color

1\ I assume there is a table -MyTable

2\Add a new measrue

Measure = SWITCH(TRUE(),
MAX([Product]) = "A" && MAX([Value]) <= 0.7, "red",
MAX([Product]) = "A" && MAX([Value]) <= 0.8, "#ffbf00",
MAX([Product]) = "A" && MAX([Value]) <= 0.9, "Green",
MAX([Product]) = "B" && MAX([Value]) <= 0.6, "red",
MAX([Product]) = "B" && MAX([Value]) <= 0.7, "#ffbf00",
MAX([Product]) = "B" && MAX([Value]) <= 0.8, "Green",
MAX([Product]) = "C" && MAX([Value]) <= 0.5, "red",
MAX([Product]) = "C" && MAX([Value]) <= 0.6, "#ffbf00",
MAX([Product]) = "C" && MAX([Value]) <= 0.7, "Green"
)

3\Add a matrix table

4\Set Condition Formatting on Value

5\Result

Option2:transposing the table and then creating conditional formatting on the new columns

1\I assume there is a table(Power Query)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI3UAWSFmDSEkjG6kQrOcHFzeCyIHFnNHFTkHgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Jan-2024" = _t, #"Feb-2024" = _t, #"March-2024" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type date}, {"A", Percentage.Type}, {"B", Percentage.Type}, {"C", Percentage.Type}})
in
    #"Changed Type1"

2\Set for Sum of A

3\For Sum of B

4\For Sum of C

5\Result

Best Regards,

Bof

[audreygerred]

Hello! You can take whatever measure you are using for the percentages and instead of putting your product field as the rows, months as teh columns and your measure in measure you can create a filtered measure for each of the products, then add each of the measures into your matrix and set it so that the values appear on rows instead of columns, then you can format each measure. This method allows you to format A, B, and C since they are each their own measure 🙂