Power BI Matrix: Color data bars based on the category value

[camelopardkh]

Dear Support!
Please suggest how to implement bars inside matrix colored depending on the category of the product. I know we can use data bars from conditional formatting options, but i could only color bars based on the positive and negative numbers. I need to use 4 colors and use them to color the products while showing the ranked bars. Any ideas? Thank you in advance!

[v-tejrama]

Hi @camelopardkh ,

Thank you @DataNinja777 for the helpful input!

Were you able to resolve the issue? If the response addressed your query, kindly confirm. This helps keep the community informed and improves solution visibility.

Thank you for your support!

[DataNinja777 · Accepted solution]

Hi @camelopardkh ,

You can accomplish this by creating a DAX measure that generates a custom SVG image for each row in your matrix. While standard conditional formatting links color to the measure's value, this method gives you full control to base the color on the product's category.

First, create a DAX measure that assigns a color to each product category. This measure uses a SWITCH function to return a specific hexadecimal color code based on the category in each row. You will need to create a new measure and replace the placeholder table and column names with your own.

_CategoryColor = 
SWITCH(
    TRUE(),
    SELECTEDVALUE('YourTable'[CategoryColumn]) = "Category A", "#1F77B4",
    SELECTEDVALUE('YourTable'[CategoryColumn]) = "Category B", "#FF7F0E",
    SELECTEDVALUE('YourTable'[CategoryColumn]) = "Category C", "#2CA02C",
    SELECTEDVALUE('YourTable'[CategoryColumn]) = "Category D", "#D62728",
    "#808080"
)

Next, create the main measure that constructs the bar image. This measure calculates the bar's length as a percentage of the maximum value among the visible rows, ensuring the bars are ranked proportionally. It then combines this percentage with the color from your _CategoryColor measure inside an SVG text string to form a colored rectangle. Create another new measure using the following code, replacing [YourMeasure] and 'YourTable'[RowHeader] with your specific fields.

Category Colored Bar = 
VAR BarColor = [_CategoryColor]
VAR CurrentValue = [YourMeasure]
VAR MaxValue = 
    CALCULATE(
        [YourMeasure],
        ALLSELECTED('YourTable'[RowHeader])
    )
VAR BarWidthPercentage = IF(CurrentValue > 0, DIVIDE(CurrentValue, MaxValue) * 100, 0)
VAR SVG = 
    "data:image/svg+xml;utf8," & 
    "<svg xmlns='http://www.w3.org/2000/svg' width='100%' height='100%'>" &
    "<rect width='" & BarWidthPercentage & "%' height='100%' fill='" & BarColor & "'/>" &
    "</svg>"

RETURN
IF(ISBLANK(CurrentValue), BLANK(), SVG)

After creating the measure, you must instruct Power BI to render its text output as an image. To do this, select your new Category Colored Bar measure in the Fields pane. This will open the 'Measure tools' tab in the ribbon. Within the 'Properties' group on this tab, find the 'Data category' dropdown menu and change its setting from 'Uncategorized' to 'Image URL'.

Finally, add the new Category Colored Bar measure to the Values field of your matrix visual. You can place it alongside your original numeric measure to display both the value and the corresponding bar. Resize the columns as needed, and sort the matrix by your original value column to see the ranked, category-colored bars in action.

Best regards,

[camelopardkh]

Thank you for the detailed instructions. I came across this idea on YouTube, but really wanted to avoid this and rather use some out-of-the-box approach in order to avoid maintenance struggles. It's interesting why PBI doesn't allow us to do it for data bars given that it is possible to achieve with other conditional formatting options such as background and font coloring. But again - thanks a lot for your answer!