Pivot Chart with Slicers

[dmacmillan]

Hello All,

Is it possible to maintain the position (and width) of columns in a stacked bar chart after applying a slicer?

Developing a Dashboard. I have a table of data including dates presented vertically (say column A). I have positioned a pivot bar chart beside the data. The presentation is fine until a slicer filters the data ... bars no longer align with months.

Seek a solution that would maintain the bars positions but, say, 'grey-out' filtered bars in the pivot chart.

Welcome assistance.

Many thanks,
David

 

[istiasztalos]

Hi,

If your axis has dates then you can change the Axis type to dates, that way it will include all dates even if there's no data

 

[dmacmillan]

Hi Istiasztalos,

Thanks for your reply.

The dates are filtered when I apply the slicer. I seek a solution that filters data for the selected (i.e. unfiltered dates) but maintains all dates in the axis label.

Welcome your thoughts.

Kind regards,
David

 

[istiasztalos]

Hi David,

what is your axis type? If set to Date, it should contain all dates in the given range, even hidden/filtered ones and those that do not have any data

 

[dmacmillan]

Hello Istiasztalos,

Thanks for your reply. My apologies, I should've advised that I had checked that. The dates (on the vertical axis) are linked to the source Data Table. The underlying data are dates, not just formatted as such.

Right-clicking the Chart Area, selecting PivotChart Options and unchecking "Autofit column widiths on update" on the Layout & Format tab had no impact either.

Maybe I'm overlooking something ... a weakness in PivotCharts vis-a-vis Charts?

 

[istiasztalos]

Hi,

I tried now with a pivot chart, indeed the setting to Date type doesn't do anything....

I could think of a workaround but not sure there's a direct setting int he pivot table (at least with Excel 201 that I have)

How about adding a helper column to your data table that would be a copy of your actual values, but set with a formula to show 0 when the row is hidden? If your actual data is in D4 for example, this is =SUBTOTAL(9,D4). SUBTOTAL measures visible cells only, when you filter the data, all values in hidden rows will be 0. If you have newer Excel version you can add the date slicer to the raw data instead of the pivot table, and the 0 value empty bars slots will remain on your chart

 

[istiasztalos]

Alternatively, you could create another table with all the possible dates (either manually listing or with formula that gets unique values from row data date column)

And then use GETPIVOT formula to get corresponding values from your Slicer filtered pivot table. You can use IFERROR to set values to 0 where the date missing from the pivot due to filtering.

 

[dmacmillan]

Hello,

Many, many thanks for efforts (persistence). Successfully used this method. Am using Excel's current version so may use the raw data method in the data table at a latter stage.

Most appreciative.

All the best,
David