Chris The Rock
Active Member
- Joined
- Feb 24, 2002
- Messages
- 287
Before I begin:
I'm not a novice by any means, but I find myself lacking in this particular area of expertise. And because of that, I don't know exactly how to succinctly ask this question in Google or on this message board. There's a good chance it's been answered - or that the answer is deceptively simple. That's what I'm hoping.
I have produced a pivot table report at work, and the results are displaying perfectly. There's one catch, though:
Using a Report-Level field as a filter, I have excluded some records from the results based on the "STATUS" field, for which I've applied a filter so only "Active" ACCOUNT records are part of the results.
However, the items that are excluded from the results still show up when the user clicks the Column Header in the ACCOUNT field. If the user clicks them, they'll get no results - as designed. This is confusing for the user, who is expecting those items not to not be part of the data to begin with. And when I say "user", of course I mean "my manager". Suggesting that my manager simply ignore those items isn't a suitable solution.
This seems to work differently than in a standard Excel Table, where when filters are applied to one field, the list of choices for filtering in the other fields are affected by the other filters in place.
So I'm looking for a way to base my pivot table on a filtered list of data, that has all items with the STATUS field not equal to "ACTIVE" already excluded from the list, or some other option to make the filtering work in a similar fashion to Excel Tables.
Before I begin a journey into learning about Power Pivot and the Excel Data Model (which very well could hold the answers for me), I want to consult with some experts here to see if I've simply missed an option, or if there's a trick to doing this that is simple to execute.
And if it is in fact done within the Data Model, any hints would be helpful. I literally have anther tab open to chandoo.org right now looking at an article on the Excel 2013 data model (hoping that it is still valid for Excel 365).
Thank you to anyone who can help, even if it's just pointing me to another post or article where this issue is addressed. I'm simply unable to find anything with my feeble question-asking skills.
I'm not a novice by any means, but I find myself lacking in this particular area of expertise. And because of that, I don't know exactly how to succinctly ask this question in Google or on this message board. There's a good chance it's been answered - or that the answer is deceptively simple. That's what I'm hoping.
I have produced a pivot table report at work, and the results are displaying perfectly. There's one catch, though:
Using a Report-Level field as a filter, I have excluded some records from the results based on the "STATUS" field, for which I've applied a filter so only "Active" ACCOUNT records are part of the results.
However, the items that are excluded from the results still show up when the user clicks the Column Header in the ACCOUNT field. If the user clicks them, they'll get no results - as designed. This is confusing for the user, who is expecting those items not to not be part of the data to begin with. And when I say "user", of course I mean "my manager". Suggesting that my manager simply ignore those items isn't a suitable solution.
This seems to work differently than in a standard Excel Table, where when filters are applied to one field, the list of choices for filtering in the other fields are affected by the other filters in place.
So I'm looking for a way to base my pivot table on a filtered list of data, that has all items with the STATUS field not equal to "ACTIVE" already excluded from the list, or some other option to make the filtering work in a similar fashion to Excel Tables.
Before I begin a journey into learning about Power Pivot and the Excel Data Model (which very well could hold the answers for me), I want to consult with some experts here to see if I've simply missed an option, or if there's a trick to doing this that is simple to execute.
And if it is in fact done within the Data Model, any hints would be helpful. I literally have anther tab open to chandoo.org right now looking at an article on the Excel 2013 data model (hoping that it is still valid for Excel 365).
Thank you to anyone who can help, even if it's just pointing me to another post or article where this issue is addressed. I'm simply unable to find anything with my feeble question-asking skills.
