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.
2017 | 2017 Forecast | 2018 | |
Entity1 | Data | ||
Entity2 | |||
Entity3 | |||
Entity4 | |||
Entity5 | |||
Entity6 | |||
<tbody>
</tbody>
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.
Any help would be greatly appreciated.
Feel free to ask questions if i didnt explain myself well enough.