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 attached.
example.xlsx
Using the table on the left, if I filter on the month of Jan, and 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 (on the right), Sam still shows even though the name doesn't even appear in the data set when filtered on the month of Jan. Is there a way to change the Pivot Table setting to reduce the available results of the data that exists on the filtered view like it does in a standard table filter? Thanks!
Bookmarks