Applying Multiple Value Filters in Excel Pivot

Question

Using Excel 2013, I would like to apply a Top 10 Filter to a pivot and also apply a filter to an additional values field. Is this possible? I would like to filter off all 1's from Sum of Individual Claims Column, but still have the top 10 by average of % of claims (minus the claim volumes that were only 1).

Table 1 with 1's in Sum of Individual Claims Column

The resulting table should look like this.

Answer 1

I found a workaround to use multiple filters in a pivot table, by using a helper column in the source data. Here is a step by step approach you can adjust for your needs.

Step 1: Add Helper Columns to the data source with a new heading and any constant value in every row. (You need one helper column per extra filter. If you want to use 2 filters, you need one helper column)

Step 2: Add the Helpercolumn attributes to your row-fields of the pivot table.

Step 3: Choose the tabular layout where all row attributes are in one row.

Step 4: Now you can apply different filters, one for each attribute in the row-field. In your case: the top 10 filter for the "Heading 1" and an unequal filter to "Help 1". This will yield the same result as if you use multiple filters for the "Heading 1".

Step 5: If you now apply this to VBA, the code could look like this:

Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables("PivotTable1")

ActiveSheet.PivotTables("PivotTable1").AllowMultipleFilters = True

With pvt.PivotFields("Heading 1")
    .ClearAllFilters
    .PivotFilters. _
    Add2 Type:=xlTopCount, DataField:=ActiveSheet.PivotTables("PivotTable1"). _
    PivotFields("Average of Heading 2"), Value1:=10
End With
With pvt.PivotFields("Help 1")
    .ClearAllFilters
    .PivotFilters.Add2 _
    Type:=xlValueDoesNotEqual, DataField:=ActiveSheet.PivotTables("PivotTable1" _
    ).PivotFields("Sum of Heading 3"), Value1:=1
End With

I hope this is useful for someone, even though the question was asked 1.5 years ago.

Answer 2

Add Individuals Claims to the Filters pane in the Pivot Fields Area.

A filter will appear in your above your Pivot table. Add unselect the value 1.

Now go to the Row Labels dropdown button and select Value Filters, then Top N...:

In the highlighted in red option select your Average of % of Claims measure.

Let me know if this helps.

Answer 3

If you need to apply multiple value filters to the same field in a pivot table, the easiest way to do that is as follows:

1. Add a column with identical values to the Source Data and add an index to its name. E.g. I had Hospital column and created its clone named Hospital 2
2. Change the Source Data to make sure the new column is included.
3. Refresh your pivot.
4. Put the new column clone (i.e. Hospital 2 in our case) into ROWS window of the PivotTable Fields tab.
5. Make sure that all fields in Columns window in PivotTable Fields tab have the following settings:enter image description here
6. Now you can apply 2 different value filters to the old and new identical columns!!!
7. If you don't need to see the newly added clone column, just hide it.

That's it!!!