Conditional formatting for clustered column chart with Legend

[MihaiH]

I have a table that shows values for different KPIs, for a specific date:

From the month and year columns I also created another column named Date.

I have one clustered column chart that shows the monthly evolution for a specific indicator for the last 3 years and in another clustered column chart below this one I want to show the growth between years - these look something like this:

My problem is that I wanted to initially conditional format the % chart to have the columns green if the value is positive and red if the value is negative. From what I read in various threads you cannot really do that, so instead I want to at least adapt the color of the Labels based on the value displayed - red for negative values, green for positive. So I've tried to format the labels by field value, based on a measure I created named KPI Label Color:

KPI Label Color = SWITCH(

TRUE(),

[Δ YoY] > 0, "Green",

[Δ YoY] < 0, "Red")

Where [Δ YoY] is the measure I created to display the growth.

The problem is that although I have negative and positive values displayed, the labels are only colored in green - I think this happens because Power BI only takes into consideration the total growth value for the whole period which is 21%, not sure exactly why.

So can anyone help?

[v-jianboli-msft]

Hi @MihaiH ,

Please try:

Final output:

Best Regards,

Jianbo Li

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

[MihaiH]

Hi @v-jianboli-msft 

Thank you for your suggestion. I tried this and it doesn't work, it shows all the labels as green, although some are positive and some are negative:

The chart looks like this:

I forgot to mention, the Δ YoY measure looks like this:

Δ YoY = SWITCH(

TRUE(),

max([Year])=2022,CALCULATE(SUM([Value]),[Year]=2022)/CALCULATE(SUM([Value]),[Year]=2021)-1,

max([Year])=2021,CALCULATE(SUM([Value]),[Year]=2021)/CALCULATE(SUM([Value]),[Year]=2020)-1)

Thank you,

Mihai

[v-jianboli-msft]

Hi @MihaiH ,

After testing your measure, it still works fine in my side:

Could you please provide me with more details about your table or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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