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

mboas

New Member
Joined
Dec 28, 2016
Messages
14
8Zd1H
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.

20172017 Forecast2018
Entity1Data
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.

9lC7exR
https://imgur.com/9lC7exR

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

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top