Pivot table for finding distinct counts with multiple page filters

[dhregan]

Hello -

I have a dynamic named range called "ERROR_TABLE" which consists of 4 columns: report name, area, owner, severity. The data in this range is updated via an upload by the user. I have created a pivot table based on this range. This pivot table - called "ERROR_PIVOT" lists a distinct count of report names with area for row and severity for column. The pivot table has 2 page-level filters, one for owner and another for severity. These page-level filters are set to "ABC" for owner and "Error" for severity.

I created the pivot table using test data which contained every possible combination of the 4 data columns, so I know the pivot table is correct. The problem that I have encountered is when one of the users loaded their data into the ERROR_TABLE range, the data did not contain any rows with an owner = "ABC", so the filter for owner is reverting back to "All" which distorts the results.

How can I keep the value of the filter static so that it does not revert to "All" when no occurrences of "ABC" are contained in the data?

TIA

 

[SpillerBD]

You're running into one of the differences between a "regular Pivot Table and a Data Model Pivot Table.
In a Normal Pivot table, you can set the "Number of items to retain per field"

You will need to force that Filter availability with another Table or Query.

 

[dhregan]

​

You're running into one of the differences between a "regular Pivot Table and a Data Model Pivot Table.
In a Normal Pivot table, you can set the "Number of items to retain per field"

You will need to force that Filter availability with another Table or Query.

Thank you. I am not familiar with how to do that. Would I copy my named range into a working table? Is this something that can be handled through VBA?

 

[SpillerBD]

Using the Data Model is better integrated with PowerBI tools in Excel. The PowerBI tools are available to o365 subscribers.
VBA would handle it using those tools, but would be waste of time imho because the PowerBI tools operate much like a program.

https://www.youtube.com/watch?v=tmTJEdRtFlA&list=PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1