Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
VamshiGoud
New Member

Row wise conditional formatting in Matrix Power bi

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

Percentage.jpg

If anyone knows the answer please let me know. 

Thanks in Advance

1 ACCEPTED SOLUTION
v-bofeng-msft
Community Support
Community Support

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

vbofengmsft_0-1725517108634.png

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

vbofengmsft_1-1725517210025.png

4\Set Condition Formatting on Value

vbofengmsft_2-1725517259843.pngvbofengmsft_3-1725517274425.png

5\Result

vbofengmsft_4-1725517298050.png

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

vbofengmsft_5-1725517435818.png

 

vbofengmsft_6-1725517435822.png

3\For Sum of B

vbofengmsft_0-1725517521656.png

 

4\For Sum of C

vbofengmsft_8-1725517454200.png

 

5\Result

vbofengmsft_9-1725517454201.png

 

Best Regards,

Bof

 

 

View solution in original post

2 REPLIES 2
v-bofeng-msft
Community Support
Community Support

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

vbofengmsft_0-1725517108634.png

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

vbofengmsft_1-1725517210025.png

4\Set Condition Formatting on Value

vbofengmsft_2-1725517259843.pngvbofengmsft_3-1725517274425.png

5\Result

vbofengmsft_4-1725517298050.png

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

vbofengmsft_5-1725517435818.png

 

vbofengmsft_6-1725517435822.png

3\For Sum of B

vbofengmsft_0-1725517521656.png

 

4\For Sum of C

vbofengmsft_8-1725517454200.png

 

5\Result

vbofengmsft_9-1725517454201.png

 

Best Regards,

Bof

 

 

audreygerred
Super User
Super User

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 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.