Y
Yoam69
Is there any way to filter pivot table items being displayed based on a set
of criteria? For example, if I have a set of sales trx for 500 customers for
the prior year, and I have created a pivot table for the data that summarizes
the total sales by Customer. I can easily sort the customers in descending
order by total sales, thus allowing me to visually see the ones I need to
filter out if, for example, I only want to see customers with total sales
over 10,000. I could then group them, and only show that group of customers
thus giving me a summary of all customers over 10,000.
However, if I then add a customer to the data that didn't exist before, and
he has sales over 10,000, he won't be in the group and must be added
manually. That can present a problem, because makes the refresh of the pivot
table take longer than it should because it's a manual process.
What I want to do is FILTER the field, similar to what you can do with the
Autofilter command. However, the filter would apply to the summarized data.
So, for example, I would apply a filter to the pivot table that only dislays
customers who have total sales over 10,000.
I could also approximate this using two pivot tables and a getpivot function
in the data relative to one of the tables...but that also creates some
confusion (or at least a hierarchy) to the order in which you update the
tables.
of criteria? For example, if I have a set of sales trx for 500 customers for
the prior year, and I have created a pivot table for the data that summarizes
the total sales by Customer. I can easily sort the customers in descending
order by total sales, thus allowing me to visually see the ones I need to
filter out if, for example, I only want to see customers with total sales
over 10,000. I could then group them, and only show that group of customers
thus giving me a summary of all customers over 10,000.
However, if I then add a customer to the data that didn't exist before, and
he has sales over 10,000, he won't be in the group and must be added
manually. That can present a problem, because makes the refresh of the pivot
table take longer than it should because it's a manual process.
What I want to do is FILTER the field, similar to what you can do with the
Autofilter command. However, the filter would apply to the summarized data.
So, for example, I would apply a filter to the pivot table that only dislays
customers who have total sales over 10,000.
I could also approximate this using two pivot tables and a getpivot function
in the data relative to one of the tables...but that also creates some
confusion (or at least a hierarchy) to the order in which you update the
tables.