Dynamic Legend colours in Stacked column chart

[vikramkumawat]

Hi everyone,

I have a stacked column chart in Power BI that shows the sum of Box Office Dollars by Year and Genre. The chart is working well, but I have a new requirement where I need the colors in the legend to dynamically adjust based on the latest year's revenue.

Specifically, I want:

1. The genre with the highest revenue in the latest year to be colored green.
2. The genre with the lowest revenue in the latest year to be colored red.
3. The colors for mid-range values to be dynamically assigned, creating a gradient effect across the revenue spectrum (e.g., transitioning from red to yellow to green).

Here is an example image of the chart I am working with:

Is there a way to achieve this in Power BI? Any guidance or workaround would be greatly appreciated!

Thanks in advance for your help!

[Ritaf1983 · Accepted solution]

Hi @vikramkumawat 

Since stacked or clustered charts don’t support conditional formatting once divided into segments, I doubt it’s feasible. The only trick I know is to leverage the new overlapping functionality, but with complex conditions like yours and more than two categories, it seems like overkill.

For extra assurance, take a look at the video in the link.
https://www.youtube.com/watch?v=MItQGef79Lc

I recommend considering the option of switching to small multiple charts by category. This approach will also be much more readable for users because both stacked charts and conditional formatting involve many elements for the reader's brain to process. Additionally, it will be much easier to apply conditional formatting and highlight what’s needed.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly