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
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
Solved! Go to 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
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
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 🙂
Proud to be a Super User! | |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
98 | |
96 | |
87 | |
68 |
User | Count |
---|---|
169 | |
133 | |
130 | |
103 | |
95 |