CODEWITHSUNDEEP
excelfilteringpivot-tablepowerpivot
Crow
Crow

Reputation: 43

Applying Multiple Value Filters in Excel Pivot

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

enter image description here

The resulting table should look like this.

enter image description here

Upvotes: 3

Views: 26949

Answers (3)

alejandro zuleta
alejandro zuleta

Reputation: 14108

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

enter image description here

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

enter image description here

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

enter image description here

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

Upvotes: 0

Axel
Axel

Reputation: 2715

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)

enter image description here

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

enter image description here

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

enter image description here

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

Upvotes: 2

Ihor Demchuk
Ihor Demchuk

Reputation: 1

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!!!

Upvotes: 0

Related Questions