3

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

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
Crow
  • 43
  • 1
  • 1
  • 6

3 Answers3

2

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
halfer
  • 19,824
  • 17
  • 99
  • 186
Axel
  • 2,545
  • 2
  • 18
  • 30
0

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • Thanks. Would this filter on the Sum of Individual claims or just each row that has an individual claim of 1? – Crow Feb 06 '17 at 23:07
  • @Crow, It filters on the Sum of Individual Claims but will filter each row in the Pivot Table not in the underlying data. – alejandro zuleta Feb 06 '17 at 23:37
  • Alejandro, I tried this and it appears to be filtering on Individual Claims, not the sum of individual claims. When I move individual claims from value to filter, it no longer sums the individual claims which is what I need to filter on. – Crow Feb 07 '17 at 18:46
  • @Crow, don't move it from **Values**, just take it from fields list again and drop it to filters. – alejandro zuleta Feb 07 '17 at 18:48
  • I tried that as well. Either way, it doesn't filter the sum of Individual Claims. – Crow Feb 07 '17 at 19:16
  • @Crow, I just tested and it works, maybe if you share a sample xlsx file I could check it – alejandro zuleta Feb 07 '17 at 19:19
  • Have you tried the reverse, Select only 1 from the filter Individual Claims? When I select only 1 I get 1's, 2's 3's etc. in the sum of individual claims values column. – Crow Feb 07 '17 at 19:23
  • @Crow, In your question you specify you need to remove the 1 Sum of Individual Claims and perform the TOP 10. Even if y select only the 1 I get 9 rows in the table, why would you need to perform a top over it? – alejandro zuleta Feb 07 '17 at 19:28
  • That data was for a visual aid only. If you look you will see that the table represents top 10, if you filter off the 1's from the sum of individual claims more data will be added creating a new top 10 by average of % of Claims – Crow Feb 07 '17 at 19:32
  • @Crow, Both filters can be applied at the same time, I just test it. Without access to your file or sample file I can't determine what is wrong with it. Good luck! – alejandro zuleta Feb 07 '17 at 20:34
0

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

Ihor Demchuk
  • 1