Forum Discussion
Helper IHelp with conditional formatting in power bi
Hi I need help with formatting of matrix table like below.
Hi Poornima2023 ,
Finally I achieved this using SVG:
This svg feature helps to give conditional formatting based on the data we have.
Here is the mcode that i used:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck0sLlHSUXLKKM0rS8xLLc4oTyxSitWJVvLLLyrJAMo4ZyTmpWSmJxZlgIWD80uhwql5eYmZSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, #"Branch " = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Branch ", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "svg", each if [Region] = "East" then "data:image/svg+xml;utf8," & "<svg width='200' height='100' xmlns='http://www.w3.org/2000/svg'>" & "<rect x='10' y='10' width='180' height='80' fill='#2196F3' stroke='#333' stroke-width='2' rx='10' ry='10'/>" & "<text x='100' y='55' font-size='20' font-family='Arial' fill='white' text-anchor='middle' dominant-baseline='middle'>East</text>" & "</svg>" else if [Region] = "North" then "data:image/svg+xml;utf8," & "<svg width='200' height='100' xmlns='http://www.w3.org/2000/svg'>" & "<rect x='10' y='10' width='180' height='80' fill='#4CAF50' stroke='#333' stroke-width='2' rx='10' ry='10'/>" & "<text x='100' y='55' font-size='20' font-family='Arial' fill='white' text-anchor='middle' dominant-baseline='middle'>North</text>" & "</svg>" else if [Region] = "South" then "data:image/svg+xml;utf8," & "<svg width='200' height='100' xmlns='http://www.w3.org/2000/svg'>" & "<rect x='10' y='10' width='180' height='80' fill='#FF9800' stroke='#333' stroke-width='2' rx='10' ry='10'/>" & "<text x='100' y='55' font-size='20' font-family='Arial' fill='white' text-anchor='middle' dominant-baseline='middle'>South</text>" & "</svg>" else null) in #"Added Conditional Column"Does this resolve your issue? let me know
Best regards,
15 Replies
sivarajan21Post Prodigy
Hi Poornima2023
Do you want expected output as below? I created a solution as below:
we can color the region and all columns as shown above image.
Please let me know and i will inform the solution for you
Poornima2023Please help. Also note its not table its matrix.
- sivarajan21
Hi Poornima2023
Sure!
I used plotly chart using python script to achieve this
You need to just click the PY in visualization and type in below code on scripts to achieve this.
Before that do you have python installed on your machine?
Currently power bi doesn't provide formatting on all columns
Please let me know
Best Regards,
- V-yubandi-msft
Community Support
Hi Poornima2023 ,
Just checking in to see how things are going with your issue. Are you still experiencing any problems, or is everything working properly now? If you need more information or help, please let us know.
Thank You. - sivarajan21
Hi Poornima2023 ,
Finally I achieved this using SVG:
This svg feature helps to give conditional formatting based on the data we have.
Here is the mcode that i used:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck0sLlHSUXLKKM0rS8xLLc4oTyxSitWJVvLLLyrJAMo4ZyTmpWSmJxZlgIWD80uhwql5eYmZSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, #"Branch " = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Branch ", type text}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "svg", each if [Region] = "East" then "data:image/svg+xml;utf8," & "<svg width='200' height='100' xmlns='http://www.w3.org/2000/svg'>" & "<rect x='10' y='10' width='180' height='80' fill='#2196F3' stroke='#333' stroke-width='2' rx='10' ry='10'/>" & "<text x='100' y='55' font-size='20' font-family='Arial' fill='white' text-anchor='middle' dominant-baseline='middle'>East</text>" & "</svg>" else if [Region] = "North" then "data:image/svg+xml;utf8," & "<svg width='200' height='100' xmlns='http://www.w3.org/2000/svg'>" & "<rect x='10' y='10' width='180' height='80' fill='#4CAF50' stroke='#333' stroke-width='2' rx='10' ry='10'/>" & "<text x='100' y='55' font-size='20' font-family='Arial' fill='white' text-anchor='middle' dominant-baseline='middle'>North</text>" & "</svg>" else if [Region] = "South" then "data:image/svg+xml;utf8," & "<svg width='200' height='100' xmlns='http://www.w3.org/2000/svg'>" & "<rect x='10' y='10' width='180' height='80' fill='#FF9800' stroke='#333' stroke-width='2' rx='10' ry='10'/>" & "<text x='100' y='55' font-size='20' font-family='Arial' fill='white' text-anchor='middle' dominant-baseline='middle'>South</text>" & "</svg>" else null) in #"Added Conditional Column"Does this resolve your issue? let me know
Best regards,
- anilgavhane
Super User
1. Add Matrix Visual
Open Power BI Desktop.
From Visualizations, choose Matrix.
Drag fields:
Rows: Region, then Branch
Columns: A, B, C, D, E (add these under Columns)
Values: Your metric (e.g., count or total value)
2. Enable Subtotals and Grand Totals
Select the Matrix.
Go to Format pane (paint roller icon).
Expand Subtotals:
Turn Row subtotals ON.
Set Per row level to ON if needed.
Expand Grand total:
Turn ON Row and Column totals if not already.
3. Apply Background Color by Region
To format each region's background color (like East = blue, South = green):
Method 1: Using Conditional Formatting
In Fields pane, right-click your value (under Values) → Conditional formatting → Background color.
Choose Format by: Field value or Rules
Use a helper column in your data model that maps Region to color (e.g., East = light blue, South = light green, etc.)
Or manually create rules like:
If Region = East → light blue
If Region = South → light green
etc.
- Poornima2023
Thanks but its not working
- KNP
Region and Branch cannot be formatted, unfortunately. Matrix visuals don't allow it.
A-E and Total can be.
See example screenshot, using a measure to define the rules and then applying to the matrix on EACH of your columns (individually).
- Poornima2023
Thanks tried it. Blanks in every region are coming white and also color is not coming on Region and branch
- KNP
As I mentioned in my comment, region and branch cannot be conditionally coloured.
The blanks you may need to change the blanks to zeros or a blank string.
- Poornima2023
Its coming like below
- V-yubandi-msft
Hi Poornima2023 ,
As KNP mentioned earlier, conditional formatting cannot be applied to row headers such as Region and Branch in the matrix visual, so those fields cannot be colored. For any blanks that appear, you can adjust your measure logic to replace them with 0 or another value, which will help ensure conditional formatting is applied consistently.
Hope this helps, and thank you for your response, KNP .
- V-yubandi-msft
Hi Poornima2023 ,
Has your issue been resolved, or do you still need assistance. If you need anything else or more information, please let us know. We're here to help.
Thank you.
- V-yubandi-msft
Hi Poornima2023 ,
Have you had a chance to review sivarajan21 response ? Please check if it meets your requirements and let us know if any changes are needed.Thanks.
