Conditional formatting in cluster column chart

Question

I have a clustered column chart with 2 datasets. One is for target and the other is value.

If the value goes above the target then the color of that bar chart must change to red, if not than green. (Refer attached screenshot).

If there any ways to achieve this in power BI?

Appreciate your support.

Answer 1

I don't believe you can custom format the individual bars on clustered bar chart as there is no 'Advanced controls' option under 'Data colours'.

I have managed to achieve your outcome by changing the graph type:

Select the 'Line and clustered column chart'

Have your target as a line and your actuals as bars:

Create a new measure in your table, this is where we will determine if a bar is above or below target.

TargetColour = 

VAR Actual = SUM(Table1[Actual])
VAR Target = SUM(Table1[Target])

RETURN

IF(
    Actual >= Target,
    1,
    0
)

Once you have done that, click on your graph and select the format section and click on 'Data colours' and then 'Advanced Controls':

Select 'Format by Rules' and select your 'TargetColour' measure in the drop down for 'Based on field' then create 2 rules:

If value is 1 THEN [Select your colour, 1 was for a value higher than or equal to the target] If value is 0 THEN [Select your colour, 0 was for a value lower than the target]

Click okay and you should see your chart update.