Line Chart Conditional Color/Shape Based on Time and Legend Breakdown?

[zerosugar]

Hi all, I'd like to do something that seems simple on the surface but is giving me a lot of grief.

I'm working with a set of data that contains revenue forecasts. These forecasts are quarterly and each forecast contains 8 quarters: 1 quarter of actual revenue (the "jump-off"), and 7 quarters of predicted revenue. This way I can also add a line that is solely for actuals data, and then have a bunch of lines for all of the forecasts and then I can see how the forecasts branch off of the actuals. These forecasts are created each quarter and have two "scenarios": a positive scenario and a negative scenario.

That's mostly fine, but I'm having trouble with the line colors/shapes in this chart. What I want is for each quarter, the Positive Scenario is a shade of blue and the Negative Scenario is shade of Red. I want the shades to be darker for more recent jump-offs, and lighter for older ones. The Legend is a key that combines the Scenario and Jump-off Name. Because I have a legend, there's no conditional formating available. Is there any way to have some kind of conditional logic behind the color/shape selection of the lines?

Here is the data:

Name Scenario Key	Forecast Name	Scenario	Start Date	Revenue Date	Revenue
Q4'21: Positive	Q4'21	Positive	12/31/2021	12/31/2021	110
Q4'21: Positive	Q4'21	Positive	12/31/2021	3/31/2022	102
Q4'21: Positive	Q4'21	Positive	12/31/2021	6/30/2022	104
Q4'21: Positive	Q4'21	Positive	12/31/2021	9/30/2022	98
Q4'21: Positive	Q4'21	Positive	12/31/2021	12/31/2022	120
Q4'21: Positive	Q4'21	Positive	12/31/2021	3/31/2023	118
Q4'21: Positive	Q4'21	Positive	12/31/2021	6/30/2023	124
Q4'21: Positive	Q4'21	Positive	12/31/2021	9/30/2023	125
Q4'21: Negative	Q4'21	Negative	12/31/2021	12/31/2021	110
Q4'21: Negative	Q4'21	Negative	12/31/2021	3/31/2022	108
Q4'21: Negative	Q4'21	Negative	12/31/2021	6/30/2022	100
Q4'21: Negative	Q4'21	Negative	12/31/2021	9/30/2022	92
Q4'21: Negative	Q4'21	Negative	12/31/2021	12/31/2022	93
Q4'21: Negative	Q4'21	Negative	12/31/2021	3/31/2023	84
Q4'21: Negative	Q4'21	Negative	12/31/2021	6/30/2023	78
Q4'21: Negative	Q4'21	Negative	12/31/2021	9/30/2023	80
Q1'22: Positive	Q1'22	Positive	3/31/2022	3/31/2022	100
Q1'22: Positive	Q1'22	Positive	3/31/2022	6/30/2022	105
Q1'22: Positive	Q1'22	Positive	3/31/2022	9/30/2022	110
Q1'22: Positive	Q1'22	Positive	3/31/2022	12/31/2022	120
Q1'22: Positive	Q1'22	Positive	3/31/2022	3/31/2023	119
Q1'22: Positive	Q1'22	Positive	3/31/2022	6/30/2023	109
Q1'22: Positive	Q1'22	Positive	3/31/2022	9/30/2023	121
Q1'22: Positive	Q1'22	Positive	3/31/2022	12/31/2023	130
Q1'22: Negative	Q1'22	Negative	3/31/2022	3/31/2022	100
Q1'22: Negative	Q1'22	Negative	3/31/2022	6/30/2022	90
Q1'22: Negative	Q1'22	Negative	3/31/2022	9/30/2022	87
Q1'22: Negative	Q1'22	Negative	3/31/2022	12/31/2022	75
Q1'22: Negative	Q1'22	Negative	3/31/2022	3/31/2023	79
Q1'22: Negative	Q1'22	Negative	3/31/2022	6/30/2023	84
Q1'22: Negative	Q1'22	Negative	3/31/2022	9/30/2023	76
Q1'22: Negative	Q1'22	Negative	3/31/2022	12/31/2023	74
Q2'22: Positive	Q2'22	Positive	6/30/2022	6/30/2022	95
Q2'22: Positive	Q2'22	Positive	6/30/2022	9/30/2022	100
Q2'22: Positive	Q2'22	Positive	6/30/2022	12/31/2022	124
Q2'22: Positive	Q2'22	Positive	6/30/2022	3/31/2023	121
Q2'22: Positive	Q2'22	Positive	6/30/2022	6/30/2023	104
Q2'22: Positive	Q2'22	Positive	6/30/2022	9/30/2023	108
Q2'22: Positive	Q2'22	Positive	6/30/2022	12/31/2023	115
Q2'22: Positive	Q2'22	Positive	6/30/2022	3/31/2024	120
Q2'22: Negative	Q2'22	Negative	6/30/2022	6/30/2022	95
Q2'22: Negative	Q2'22	Negative	6/30/2022	9/30/2022	90
Q2'22: Negative	Q2'22	Negative	6/30/2022	12/31/2022	91
Q2'22: Negative	Q2'22	Negative	6/30/2022	3/31/2023	84
Q2'22: Negative	Q2'22	Negative	6/30/2022	6/30/2023	73
Q2'22: Negative	Q2'22	Negative	6/30/2022	9/30/2023	67
Q2'22: Negative	Q2'22	Negative	6/30/2022	12/31/2023	69
Q2'22: Negative	Q2'22	Negative	6/30/2022	3/31/2024	72
Actual	Actual	Actual		12/31/2021	110
Actual	Actual	Actual		3/31/2022	100
Actual	Actual	Actual		6/30/2022	95

Here's what I have now (I have manually set the colors):

I have an issue when I select a sub-section of forecasts. The colors aren't dynamic so if I select Q4'21 and Q1'22, I would like Q1'22 to be the darkest possible shade of red and blue, but it doesn't do that. It displays as the color that I had hardcoded it to be:

Is there any way to have conditional color and shape formatting when I have a legend? I am also planning on using a linked table (created using CROSSJOIN) to have a conditional legend for this table, so that I can split the legend by either "Name Scenario Key" or "Forecast Name" - That will allow me to see time series of total or it's sub-parts. Not sure if that will affect the logic. 

I would appreciate any help on this! As far as I know, there's no way to have conditional line colors, but hopefully I'm wrong. Thank you!

[v-rongtiep-msft · Accepted solution]

Hi @zerosugar ,

As far as I concerned, There are no such visual for your requriement. You can submit a requirement here. Thanks for understanding.

Or you can change  the line chart to bar chart, then use conditional formatting.

Power BI line chart marker color conditional formatting - Data Cornering

Conditional Formatting for Color in Bar Chart - Microsoft Power BI Community

Best Regards

Community Support Team _ Polly

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