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.)

Row Labels	Rate
2004	100.00%
F	0.76%
P	99.24%
2005	100.00%
F	7.19%
P	92.81%
2006	100.00%
F	0.71%
P	99.29%
2007	100.00%
F	3.60%
P	96.40%
2008	100.00%
F	7.91%
P	92.09%
2009	100.00%
F	2.90%
P	97.10%
2010	100.00%
F	8.09%
P	91.91%
2011	100.00%
F	2.86%
P	97.14%
2012	100.00%
F	3.68%
P	96.32%
2013	100.00%
F	2.90%
P	97.10%
2014	100.00%
F	4.41%
P	95.59%
Grand Total

<colgroup><col><col></colgroup><tbody>
</tbody>

Row Labels	Rate
2004	100.00%
P	100.00%
2005	100.00%
P	100.00%
2006	100.00%
P	100.00%
2007	100.00%
P	100.00%
2008	100.00%
P	100.00%
2009	100.00%
P	100.00%
2010	100.00%
P	100.00%
2011	100.00%
P	100.00%
2012	100.00%
P	100.00%
2013	100.00%
P	100.00%
2014	100.00%
P	100.00%
Grand Total

<colgroup><col><col></colgroup><tbody>
</tbody>

Row Labels	Rate
2004	100.00%
P	99.24%
2005	100.00%
P	92.81%
2006	100.00%
P	99.29%
2007	100.00%
P	96.40%
2008	100.00%
P	92.09%
2009	100.00%
P	97.10%
2010	100.00%
P	91.91%
2011	100.00%
P	97.14%
2012	100.00%
P	96.32%
2013	100.00%
P	97.10%
2014	100.00%
P	95.59%
Grand Total

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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!