Pivot Charts resetting when i change filters through slicers, how to workaround?

[mboas]

Hello MrExcel.

I have a dataset of 70k rows and 40 columns describing 40 different entities, which i am analyzing through a pivot table.

I am trying to make a dashboard where i can describe and compare whichever entities i want through a slicer controlling multiple pivots.

However, for instance if i want to compare Net Revenue with the forecast in a combo chart, whenever i change the filters for which entities i want to show, the chart structure resets from being column/line (for forecast) to being pure column.
From my understanding, this is unavoidable in excel for some unknown reason.

I am trying to do a workaround, but i'm having trouble creating a normal chart that adjusts to how many entities i have chosen.

Below is a data example, both the column headers and the row headers need to be dynamic so i can swap between looking at years and quarters, and the entities i need to be able to swap between exactly which entities i want to show.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2017[/TD]
[TD]2017 Forecast[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Entity1[/TD]
[TD]Data[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Entity2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Entity3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Entity4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Entity5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Entity6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The setup i have right now correctly only shows the entities i have chosen, however it still fills out with multiple 0 value placeholders as shown in below picture.

https://imgur.com/9lC7exR

Any help would be greatly appreciated.
Feel free to ask questions if i didnt explain myself well enough.