How to Reduce Filtered Results in Pivot Table??

[clayjm]

I feel like this should be an automatic feature in a pivot table. If I'm viewing a normal table of data with a filter, the results are reduced based on filter for other fields. See the simple example below.

If I filter on the month of Jan, and then click on then click on the drop down for SalesRep, I'm only given the three names that apply to that month as an option:

As you can see, "Sam" is not an option because the name does not exist for Jan. However, if I use a pivot table based on the original data, the pivot table will show all the names, even when filtered just on Jan.

As you can see in the pivot table above, Sam still shows even though the name doesn't even appear in the data set that I'm filtered on. Is there a way to change the Pivot Table setting to reduce the available options based on the data that exists on the filtered view like it does in a standard table filter? Thanks!

 

[clayjm]

(Sorry, the images are not working for me… not sure how to attach screenshots. Here is the table example)

I feel like this should be an automatic feature in a pivot table. If I'm viewing a normal table of data with a filter, the results are reduced based on filter for other fields. See the simple example below.

Month	SalesRep	Sales
Jan	Bob	$ 500.00
Feb	Bob	$ 300.00
Jan	Sue	$ 200.00
Feb	Sue	$ 400.00
Mar	Sue	$ 700.00
Jan	Fred	$ 100.00
Mar	Fred	$ 500.00
Mar	Sam	$ 600.00

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

If I filter on the month of Jan, and then click on then click on the drop down for SalesRep, I'm only given the three names that apply to that month as an option:

As you can see, "Sam" is not an option because the name does not exist for Jan. However, if I use a pivot table based on the original data, the pivot table will show all the names, even when filtered just on Jan.

As you can see in the pivot table, Sam still shows even though the name doesn't even appear in the data set that I'm filtered on. Is there a way to change the Pivot Table setting to reduce the available options based on the data that exists on the filtered view like it does in a standard table filter? Thanks!