excel - Can you filter Pivot Table by Sub-Total (say ">60 hrs"), but display lesser constituent records

admin管理员组

文章数量:1335877

So, I'm trying to total bunch of hours in various 1-week pay periods using a Pivot Table in Excel 2016/2019 desktop.

Currently it shows individual clients that a provider works with, and how many hours the provider worked with those clients in a week. It then sub-totals the hours for the week across all clients for that provider.

I can filter the total column for "Hours>60" [with a little finagling, since it doesn't give the option by default] but it hides the individual Customer Hours lines if they're under 60 hours individually (it shows any customers a provider worked with individually for more than 60 hours, but not if they worked with that customer for less than 60 hours), and only shows the total for the week.

This kind of "blows holes," of a sort, in the pivot table for display purposes. That is to say, any time a provider works less than 60 hours with an individual client, the pivot table filters out the name of the customer and the case manager they works with and the specific number of hours the provider worked with that client, and only shows just the Sub-Total itself.

So, like if we have:

Customer 1: 61 hrs.

It'll display 2 lines:

Customer 1: 61 hrs.

Total: 61 hrs.

But if we have:

Customer 1: 30 hrs.

Customer 2: 30 hrs.

It'll only display 1 line:

Total: 60 hrs.

Or, if we have:

Customer 1: 12 hrs.

Customer 2: 61 hrs.

Customer 3: 2 hrs.

It'll basically show (due to filtering the Total column for ">60"):

Customer 2: 61 hrs.

Total: 75 hours.

Leaving off the actual customer hours lines, and the relevant other information on those lines (like who their case manager is, etc.), if the particular customer line doesn't exceed the Filter threshold (even though the Sub-Total *does).

Ideally, what I'd like is to show all of the relevant customer hours lines, where the total hours worked by the provider exceeds 60 hrs, regardless of whether they worked more than 60 hours with any of the individual customers. So:

Customer 1: 30 hrs.

Customer 2: 30 hrs.

Total: 60 hrs.

or

Customer 1: 12 hrs.

Customer 2: 61 hrs.

Customer 3: 2 hrs.

Total: 75 hours.

etc.

But exclude any Subtotals less than 60 hrs (and the related constituent Customer hours lines which would also be less than 60, since they don't add up to 60)...

Is that something Pivot Tables can do? Feel like they should somehow be able to do that? Just not sure if they actually can or not?

本文标签：