Conditional Format Bars and Line in a combo chart by year

[MWC914]

Is there a way to format a combo chart to differentiate the bars and line conditionally based on the year?

As you can see in the first screenshot, each year is a different color, and the average line matches the color of the bar. 

While I see the option to format the column in power bi based on a function, I do not see anything for the line. 

Desired Outcome FormatPower BI

[Anonymous]

Hi  @MWC914 ,

It's not clear what you mean, if you want to highlight the specified data of the legend, you can click on it with the left mouse button. If the above results do not meet your expectations, you can express your expectations in the form of a picture, we can help you better!

Best Regards,

Liu Yang

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

[MWC914]

I meant if you could make the legend only display the value for the column, in this case Value1, and subsquently remove the individual line segments (2023year, 2024year, etc.). My data would be going back as far as 2015 and I would not want all 10 years showing up in the legend, just the column. 

[Anonymous]

Thanks for the reply from PhilipTreacy , please allow me to provide another insight: 
Hi  @MWC914 ,

I created some data:

As far as I know, Line Chart and Clustered Column Chart doesn't support customizing the color of Line y-axis, this is due to the design of Power BI, you can use the following method instead: multiple measures are created, and each measure only displays the data corresponding to the year, otherwise it is empty.

Here are the steps you can follow：

1. Create measure.

2023year =
var _mindate=MINX(FILTER(ALL('Table'),YEAR('Table'[Date])=2023),[Date])-1
var _maxdate=MAXX(FILTER(ALL('Table'),YEAR('Table'[Date])=2023),[Date])
return
IF(
    MAX('Table'[Date])>=_mindate&&MAX('Table'[Date])<=_maxdate,SUM('Table'[VALUE2]),BLANK())

2024year =
var _mindate=MINX(FILTER(ALL('Table'),YEAR('Table'[Date])=2024),[Date])-1
var _maxdate=MAXX(FILTER(ALL('Table'),YEAR('Table'[Date])=2024),[Date])
return
IF(
    MAX('Table'[Date])>=_mindate&&MAX('Table'[Date])<=_maxdate,SUM('Table'[VALUE2]),BLANK())

2. Lines - Apply settings to (select specified measure name) - Color (set different color).

3. Result:

Best Regards,

Liu Yang

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

[MWC914]

Thank you very much, that's a very clever workaround!

Do you know if there is a way to certain items from the legend? I just looked and did not see that as an option. I could always insert a text box but would prefer to have a more automated solution. 

[PhilipTreacy]

@MWC914 

Download PBIX file with the example below

Currently it's not possible to apply a CF function to the line.  It's all one colour you manually choose.

You can create a rule for the columns though.

Create this measure

CF Year = 

VAR _year = YEAR(MAX('DataTable'[Date]))

RETURN

SWITCH(

    TRUE(),

    _year = 2011, "magenta",

    _year = 2012, "green",

    _year = 2013, "blue",

    _year = 2014, "black"

)

then create a CF rule like this

Giving this

Regards

Phil