Search code examples
powerbipowerbi-desktop

How to conditionally format color based on a categorical value?

I am creating table visualizations in Power BI. I know how to use conditional formatting to automatically set colors based on numerical values, but I want to color the rows of my visualization based on a categorical variable.

In my table there is a column that identifies a "department". I would like to highlight all rows of a certain department in some color, but since it isn't a numerical variable I have no idea how to do this.

How can I use conditional formatting in my table visualization with a categorical variable?

Solution

  • As you said, Power BI doesn't support conditional formatting based on categorical values. What I come up with is to assign them numerical values accordingly.

    Let's say we have a table like this:

    data

    We can add a column called Department Code (You can just label the departments you want to highlight as well):

    Department Code = 
SWITCH(
    'Table'[Department],
    "Production", 1,
    "Research and Development", 2,
    "Purchasing", 3,
    "Marketing", 4,
    "Human Resource Management", 5,
    "Accounting and Finance", 6,
    0
)

    department code

    And now you can define color by rules for Department based on Department Code (For summarization you can just pick Min/Max/Average):

    conditional formatting

    Now we have Marketing in red and Accounting and Finance in yellow:

    results