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.)
<colgroup><col><col></colgroup><tbody>
</tbody>
<colgroup><col><col></colgroup><tbody>
</tbody>
<colgroup><col><col></colgroup><tbody>
</tbody>
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>