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]

 

[Fazza]

hi

A work around, maybe: can you leave the unwanted data in the chart but format it to be invisible?

hth

 

[mmclandy]

Thanks for the suggestion, Fazza, it's a good idea for the table. But since what I really want is for the chart to show only P values, I don't think that'll work.

Any other suggestions? Thank you!

 

[Jerry Sullivan]

You could add a Calculated Item (different from a Calculated Field), then just show that field's series in the chart.

The Calculated Item could be named "RateOfP" and have the formula: =P /(P +F )

If you take this approach your Pivot Table should not be used for SubTotals or GrandTotals since the Calculated Item is added to the totals.

 

[Fazza]

Thanks for the suggestion, Fazza, it's a good idea for the table. But since what I really want is for the chart to show only P values, I don't think that'll work.

Any other suggestions? Thank you!

I understand from your comment that my suggestion won't work for you.

Though we may or may not be aligned on my suggestion - but just in case it does help, I was talking about the chart not the table.

regards

 

[mmclandy]

Thank you Fazza. I think I may not understand your suggestion, or perhaps I did not explain my situation clearly. I don't appear to be able to hide the F in the chart. Here is what my chart looks like. Since F and P are on the same line, it doesn't appear to be possible to make F invisible. Thanks very much for your help,
Margo

ETA: I guess i can't add a picture of my chart. Sorry! But F and P are both part of the same column.

I understand from your comment that my suggestion won't work for you.

Though we may or may not be aligned on my suggestion - but just in case it does help, I was talking about the chart not the table.

regards

 

[mmclandy]

Jerry, thank you for your suggestion. I have played around for hours with the calculated fields and calculated items (both before posting and after reading your suggestion) but I just don't seem to be able to make it work. First of all, usually when I try to add a calculated item, it won't let me, and gives me the error message that a field can only appear once (this happens even when every field appears once!).

Second, even when I try to add calculated items and calculated fields, it doesn't work out. I think maybe because in the data, there is one column of Ps and Fs, and in the pivot table, it is counting Ps and Fs in this one column. Then when I try to put in a calculated field/item, there can't actually be a calculation?

As you may tell, I am feeling lost. I have pretty good facility with formulas and charts and excel databases, but I am thinking Pivots are a bit out of my comfort zone.

Thanks very much.

 

[circledchicken]

Hi mmclandy,

I think the suggestions by Fazza and Jerry Sullivan should both work. Maybe post a small sample workbook on Skydrive for example and I or someone else may be able to provide a sample implementation.

 

[mmclandy]

Circledchicken,
Thanks for the suggestion. Here is a link to the workbook on skydrive:
https://skydrive.live.com/redir?resid=451D450EFA9584F!107&authkey=!ABKWoi_agibfNhc&ithint=file,.xlsx

Thank you VERY much in advance for your help.

 

[circledchicken]

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