Forum Discussion

TODEI's avatar
TODEI
Frequent Visitor
4 years ago

Combo chart with multiple filtered lines

Hej Power BI community,

 

I am trying to compare multiple forecasts with each other including the actuals as columns. 

 

My data looks as following:

 

Year & Month Planning PeriodActual m³Forecasted m³

2022 May

2022 May10001100
2022 May2022 Jun10001000
2022 May2022 Jul10001000
2022 Jun2022 May12001150
2022 Jun2022 Jun12001250
2022 Jun2022 Jul12001200
2022 Jul2022 May01300
2022 Jul2022 Jun01350
2022 Jul2022 Jul01320

...

 

So basically, any Year Month combination that hs already passed during planning has the actuals instead of a forecast. 

 

Now what I would love to do is have one line for each planning period (forecast) and one column per month for the actuals. I created a new actuals using this formula so it does not add up different months actuals. 

 

 

 

m3 Single = Divide([m3 Actuals], IF(ISFILTERED(Table[Planning Period]), COUNTROWS(VALUES(Table[Planning Period])),0))

 

 

 

This is pretty much the outcome I want:

 

 

To achieve this I had to create one measure per forecasting round. Also, each measure I do not have active or that is filtered out still gets shown on the legend. This is the formula used:

 

 

 

m3 2021 Sep = CALCULATE([m3 Forecast],Planning Period[Planning Period]="2021 Sep")

 

 

 

Since I have atleast 20 planning periods per year I would love not to create a measure for each line. Is there any option to do this?

 

I found one visual by modular GmbH that does this. Sadly it always aligns the actuals with a different Axis and I cannot seem to get the axes to align.

 

 

Thank you very much for your help!