Forum Discussion

Poornima2023's avatar
Poornima2023
Helper I
11 months ago
Solved

Help with conditional formatting in power bi

Hi I need help with formatting of matrix table like below.  
  • sivarajan21's avatar
    sivarajan21
    11 months ago

    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&colon;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&colon;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's avatar
anilgavhane
Super User
11 months ago

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's avatar
Poornima2023
Helper I
11 months ago

Thanks but its not working