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

mmclandy

New Member
Joined
Jan 30, 2014
Messages
6
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
2004100.00%
F0.76%
P99.24%
2005100.00%
F7.19%
P92.81%
2006100.00%
F0.71%
P99.29%
2007100.00%
F3.60%
P96.40%
2008100.00%
F7.91%
P92.09%
2009100.00%
F2.90%
P97.10%
2010100.00%
F8.09%
P91.91%
2011100.00%
F2.86%
P97.14%
2012100.00%
F3.68%
P96.32%
2013100.00%
F2.90%
P97.10%
2014100.00%
F4.41%
P95.59%
Grand Total

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


Row LabelsRate
2004100.00%
P100.00%
2005100.00%
P100.00%
2006100.00%
P100.00%
2007100.00%
P100.00%
2008100.00%
P100.00%
2009100.00%
P100.00%
2010100.00%
P100.00%
2011100.00%
P100.00%
2012100.00%
P100.00%
2013100.00%
P100.00%
2014100.00%
P100.00%
Grand Total

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

Row LabelsRate
2004100.00%
P99.24%
2005100.00%
P92.81%
2006100.00%
P99.29%
2007100.00%
P96.40%
2008100.00%
P92.09%
2009100.00%
P97.10%
2010100.00%
P91.91%
2011100.00%
P97.14%
2012100.00%
P96.32%
2013100.00%
P97.10%
2014100.00%
P95.59%
Grand Total

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

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,192
hi

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

hth
 

mmclandy

New Member
Joined
Jan 30, 2014
Messages
6
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,192
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

New Member
Joined
Jan 30, 2014
Messages
6
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
 
Last edited:

mmclandy

New Member
Joined
Jan 30, 2014
Messages
6
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

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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.
 

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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
 

Forum statistics

Threads
1,082,275
Messages
5,364,178
Members
400,785
Latest member
Mahar92

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top