Combo Chart with Filters

[UreAwesome]

Hello!
I have a pivot with 2 filters set up with count and percent of row total to show percent on time. I created a pivot chart so if someone selects their facility in one of the filters, the chart will update to show their count/percent for last year. I have the counts set up as the stacked columns and the percentages are lines as the secondary axis. If I filter for a facility that has a 100% timeliness, the chart automatically defaults back to a stacked bar chart, eliminating the secondary axis as a line chart.
So in the example file attached, if you filter for Tallahassee, the chart works perfect. They aren't a 100%. Then when you filter for Chicago, the secondary axis changes to a bar chart.
Is there a way to stop the chart from doing this?

Thank you in advance!

 

[Faseeh]

Hello,

The problem was in your data and not in pivot table. If you look at your data, some of the cities were missing either of the age categories. For Chicago, there was no entry for age bracket >= 2, similarly for London there was no entry for bracket 0-1.

When you are making pivot table and you want it not to show any dis-consistency then do make an entry even if there is no or zero value available for the variable - just to make data consistent and give pivot all possible combinations for all the variables.

Plz see if attachment works.

 

[UreAwesome]

Hi Faseeh,
Thank you for taking a look at this. If I were to instead of using age group column, use age and then group that in the pivot table, is there a way to tell the pivot if there is no data in the age group >2, show it as zero instead of that group not appearing at all in the pivot? Like you did by adding those additional records in red to force the pivot to count it as zero? This was just a small data set I used for an example. I am actually working with hundreds of sites over at least a year time frame, so there is a lot more data. I would have to do a lot of scrubbing to make this work.
My creative temporary solution was to create two pivots and two pivot charts. One for the volume and one for the percentage. then I overlapped them, did some color editing so only the lines would show for the percentage. Then used slicers to connect both together. It seems to work ok, but it was a lot of work around!!

 

[Chihiro]

For PivotTable you can set what to show in case of empty cells or error values.

Click on PivotTable and go to PivotTable Tools->Options.

There you will see Format Section where you can specify.


Edit: However, if there is no entry for specific item/column then it will not show up. Good data setup is key to proper data analysis.