Pivot Table -- I need the "show value as" to stay the same even when some values are filtered

[mmclandy]

Hi,
I have a Pivot table in which I am tracking the rate at which two different values (P and F) occur annually. However, I only need to graph the rate at which P occurs. I have been able to show the rates in my pivottable by using the "show values as % of parent total." The problem I'm running into now is: I want to create a graph that shows (by year) the rate of P. When I create a PivotChart, it shows both P and F. So, I filter out F. But then the rate for P goes up to 100% because F has been filtered out!

My question, then, is this: is there a way to hold fixed the "rate" even when filtering out some values? Below, I am pasting three tables: (1), the way it looks initially. (2), the way it looks after I filter, and (3) the way I WISH it looked! Thanks VERY much in advance for any help. (To clarify, what I care about is the CHART, but filtering on the chart correspondingly filters the pivot table.)

[TABLE="width: 149"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row Labels
[/TD]
[TD]Rate
[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]0.76%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]99.24%[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]7.19%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]92.81%[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]0.71%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]99.29%[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]3.60%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]96.40%[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]7.91%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]92.09%[/TD]
[/TR]
[TR]
[TD]2009[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]2.90%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]97.10%[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]8.09%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]91.91%[/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]2.86%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]97.14%[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]3.68%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]96.32%[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]2.90%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]97.10%[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD="align: right"]4.41%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]95.59%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 149"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]2009[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 153"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD="align: right"]2004[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]99.24%[/TD]
[/TR]
[TR]
[TD="align: right"]2005[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]92.81%[/TD]
[/TR]
[TR]
[TD="align: right"]2006[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]99.29%[/TD]
[/TR]
[TR]
[TD="align: right"]2007[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]96.40%[/TD]
[/TR]
[TR]
[TD="align: right"]2008[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]92.09%[/TD]
[/TR]
[TR]
[TD="align: right"]2009[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]97.10%[/TD]
[/TR]
[TR]
[TD="align: right"]2010[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]91.91%[/TD]
[/TR]
[TR]
[TD="align: right"]2011[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]97.14%[/TD]
[/TR]
[TR]
[TD="align: right"]2012[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]96.32%[/TD]
[/TR]
[TR]
[TD="align: right"]2013[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]97.10%[/TD]
[/TR]
[TR]
[TD="align: right"]2014[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD="align: right"]95.59%[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Grand Total[/TD]
[/TR]
</tbody>[/TABLE]

 

[mmclandy]

Circledchicken, thank you SO much. You are a life-saver. I can use this implementation in so many ways. Appreciate your invaluable help.

Please see a possible implementation of Fazza's suggestion here:
http://sdrv.ms/MTAOsZ

The steps I took were:
-> Moved the 'P or F?' field into the column labels area
-> Changed the 'Show Values As' option to 'Percentage of Parent Column Total'
-> Changed the chart type to a column chart (I thought this was more appropriate but you can change it back to a line chart if you want)
-> Formatted the F data series to have no fill and no border line
-> Set the Series Overlap property to 100%
-> Deleted F from the legend box on the chart

 

[circledchicken]

You're welcome (it was Fazza's initial idea). Thanks for the feedback as well!