Values disappear when using filter in pivot table & chart

[Kimber]

My Pivot Table layout:
REPORT FILTER: blank
COLUMN LABELS: Values
ROW LABELS: Location Description, then Reason
VALUES: Sum of Cost, Sum of Qty

I have sorted my table by location descriptions in descending order of sum of cost and all locations with reasons with cost and quantities are filled. When I use the down arrow on the Row Labels and uncheck Select All and select one or more Locations, the result is an empty pivot table. However, if I highlight specific rows for one of more locations, I can right-click, hover over Filter and select "Keep only selected items" and the table shows the appropriate selection(s)

I email this table and chart to upper-level managers, so I cannot filter for them. I'm hoping this is a user problem that I can fix with your help.

 

[Debraj]

Yes.. its obvious..

because, when you apply filter in Row label, with multiple Fields, the top filter show only filters from selected Field or active field.
there may be filter already applied for another field..

but when you select multiple item, from multiple Filed in same ROW LABEL, and apply "Keep Only selected Item..
Excel pivot run through all the levels and then according to selection, it apply Filter in multi Level.

Post your file with empty pivot table.. we can show you.. whats actual filter is currently applied in which level..

 

[Kimber]

Thank you, Debra. I've uploaded a copy of my file with random values and generic descriptions. I look forward to your comments.

 

[NARAYANK991]

Hi ,

See if your file is OK now.

Narayan

 

[Kimber]

Hi ,

See if your file is OK now.

Narayan

Thank you, Narayan. This does work; however, the end users want to see the area grouped with the reasons. Is using the location as the report filter the only way to prepare this? When I filter a reason, the area doesn't show.

 

[NARAYANK991]

Hi ,

Not at all. See the file now. Both the location description and the region are in the row labels. When you click on the arrow to select , first you need to select whether you want to choose among the various location descriptions or you want to choose among the various regions. Thereafter , you can choose the different areas or the different reasons. Whatever you choose , you will get a report showing the different areas and within each area , the different reasons.

Narayan