Forum Discussion

soni_b's avatar
soni_b
Regular Visitor
7 months ago
Solved

Set predefined legend colours using formatting or calculated column for stacked column chart

I'm working with a stacked column chart in Power BI where the legend represents different predefined categories. The challenge I'm facing is that not all categories are present in the current dataset, but I still want to predefine the colors for all possible legend items, including those that may appear in future data.

 

Currently, under Format → Columns, I can manually assign colors to categories that exist in the data (e.g., Category 1 and Category 2). However, there's no option to set colors for categories that aren't currently present, like Category 3. This leads to inconsistent coloring when new categories show up later, the chart looks messy and loses visual consistency.

I’ve noticed that there’s no “fx” button or conditional formatting option available for legend colors in stacked column charts. Is there a workaround or best practice to predefine legend colors, perhaps using a calculated column, a mapping table, or some other method?

 

 

 

 

Need Help
  • Mauro89's avatar
    Mauro89
    7 months ago

    Hi soni_b,

     

    The key is to ensure all categories exist in your data model, even if they have zero values in the current dataset. Here's how you can try to achieve it:

    Create a complete category reference table

    1. Create a new table with all possible categories:
     
    DAX
    CategoryColors = DATATABLE(
    "Category", STRING,
    "ColorCode", STRING,
    {
        {"Category 1", "#00FF00"},
        {"Category 2", "#FF0000"},
        {"Category 3", "#0000FF"},
        {"Category 4", "#FFFF00"}
    }
)
    1. Create a relationship between this table and your fact table
    2. Use the category from this reference table in your visual's legend
    3. Set colors manually in Format → Columns - they'll persist even when categories have no data

    Best regards!

    PS: If you find this post helpful consider leaving kudos or mark it as solution

5 Replies

  • Mauro89's avatar
    Mauro89
    Super User
    7 months ago

    Hi soni_b,

     

    The key is to ensure all categories exist in your data model, even if they have zero values in the current dataset. Here's how you can try to achieve it:

    Create a complete category reference table

    1. Create a new table with all possible categories:
     
    DAX
    CategoryColors = DATATABLE(
    "Category", STRING,
    "ColorCode", STRING,
    {
        {"Category 1", "#00FF00"},
        {"Category 2", "#FF0000"},
        {"Category 3", "#0000FF"},
        {"Category 4", "#FFFF00"}
    }
)
    1. Create a relationship between this table and your fact table
    2. Use the category from this reference table in your visual's legend
    3. Set colors manually in Format → Columns - they'll persist even when categories have no data

    Best regards!

    PS: If you find this post helpful consider leaving kudos or mark it as solution

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    7 months ago

    1) Create a Category → Color mapping table

    Example:

    Category ColorHex
    Category 1#4472C4
    Category 2#ED7D31
    Category 3#70AD47

    • This table can be disconnected

    • Or related on Category

     

    2) Use conditional formatting on Data colors

    For a stacked column chart:

    • Format → Data colors

    • Click fx

    • Format by: Field value

    • Based on field: ColorHex

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    7 months ago

    soni_b 

    does that mean your data comes from several data tables? maybe you can create a dim category table and connect it to different data tables and add the category column from dim table to the chart. 

     

    if this does not work, pls provide some sample data and expected output.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 months ago

    Hi soni_b ,

    Thank you for reaching out to the Microsoft Fabric Community Forum. 

     

    Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided. If you still require support, please let us know, we are happy to assist you. Thank you for all the members who have already provided helpful responses.

     

    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 months ago

    Hi soni_b ,

     

    We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
    If you still require support, please let us know, we are happy to assist you.

     

    Thank you.