PivotTable's Report Filter using "greater than"

Question

I have a pivot table which has one of the fields (Probability) in a Report Filter. Its values are percentages in step of 5 (0,5,10,15,...,100). I'd like to use it to filter probabilities greater than or equal a certain value, but the filter only filters exact choices.

For now I use a workaround of allowing multiple values, and then selecting all values from the threshold I want, all the way to 100 This solution, apart from being awkward, doesn't show my selection, which is necessary as this table is being printed out.The Filter's display value is "(Multiple Values)" and I'd like to show all the values selected, or even better, something like ">=20%". I don't really care if they show in the field itself or in another cell outside the Pivot table.

My questions: 1) can I get the filter to filter on >= of my seletion? If not 2) Can I show the multiple selections like ">=20%"

score 15 · Answer 1 · edited Feb 09 '17 at 20:02

I know this is a bit late, but if this helps anybody, I think you could add a column to your data that calculates if the probability is ">='PivotSheet'$D$2" (reference a cell on the pivot table sheet).

Then, add that column to your pivot table and use the new column as a true/false filter.

You can then change the value stored in the referenced cell to update your probability threshold.

If I understood your question right, this may get you what you wanted. The filter value would be displayed on the sheet with the pivot and can be changed to suit any quick changes to your probability threshold. The T/F Filter can be labeled "Above/At Probability Threshold" or something like that.

I've used this to do something similar. It was handy to have the cell reference on the Pivot table sheet so I could update the value and refresh the pivot to quickly modify the results. The people I did that for couldn't make up their minds on what that threshold should be.

score 4 · Answer 2 · answered Nov 27 '13 at 06:10

4

In an Excel pivot table, you are correct that a filter only allows values that are explicitly selected. If the filter field is placed on the pivot table rows or columns, however, you get a much wider set of Label Filter conditions, including Greater Than. If you did that in your case, then the added benefit would be that the various probability levels that match your condition are shown in the body of the table.

answered Nov 27 '13 at 06:10

MattClarke

1,647
1
11
32

Unfortunately the Probability has to stay at the Page level. Is there any way to get at the selected filter values through VBA? This way I could display them in a cell outside the table... – Lukasz Nov 27 '13 at 21:49
I'm not sure what you mean by "stay at the Page level". – MattClarke Nov 28 '13 at 06:42
I mean it has to stay in the Report Filter section. – Lukasz Nov 28 '13 at 14:28
One approach would be to start with my suggestion about including the probabilities in the pivot table rather than the filter, then do something that concatenates all the values in that column of the pivot table and shows that concatenated string back in a cell above the pivot table. The kind of concatenation I have in mind is discussed in http://stackoverflow.com/questions/8135995/how-to-merge-all-column-into-one-cell-in-excel – MattClarke Nov 28 '13 at 22:02

score 1 · Answer 3 · answered Aug 27 '20 at 17:31

1

One way to do this is to pull your field into the rows section of the pivot table from the Filter section. Then group the values that you want to keep into a group, using the group option on the menu. After that is completed, drag your field back into the Filters section. The grouping will remain and you can check or uncheck one box to remove lots of values.

answered Aug 27 '20 at 17:31

Jerry Boettcher

11
1

Exactly what I was looking for, this should have more upvotes – bavShehata Jul 03 '22 at 06:51

score 0 · Accepted Answer · answered Nov 28 '13 at 18:58

After some research I finally got a VBA code to show the filter value in another cell:

Dim bRepresentAsRange As Boolean, bRangeBroken As Boolean
Dim sSelection As String
Dim tbl As Variant
bRepresentAsRange = False
bRangeBroker = False

With Worksheets("Forecast").PivotTables("ForecastbyDivision")
            ReDim tbl(.PageFields("Probability").PivotItems.Count)
            For Each fld In .PivotFields("Probability").PivotItems

                If fld.Visible Then
                    tbl(n) = fld.Name
                    sSelection = sSelection & fld.Name & ","
                    n = n + 1
                    bRepresentAsRange = True
                Else
                    If bRepresentAsRange Then
                        bRepresentAsRange = False
                        bRangeBroken = True
                    End If
                End If

            Next fld

            If Not bRangeBroken Then
                Worksheets("Forecast").Range("ProbSelection") = " >= " & tbl(0)
            Else
                Worksheets("Forecast").Range("ProbSelection") = Left(sSelection, Len(sSelection) - 1)
            End If

        End With

score 0 · Answer 5 · answered Jul 24 '17 at 04:12

Maybe in your data source add a column which does a sumif over all rows. Not sure what your data looks like but something like =(sumif([column holding pivot row heads),[current row head value in row], probability column)>.2). This will give you a True when the pivot table will show >20%.
Then add a filter on your pivot table on this column for TRUE values

score 0 · Answer 6 · edited Oct 14 '19 at 12:51

I can't say how much this might help you, but just found a solution to something similar problem which I faced. In the Pivot-

Right click and choose Pivot table options
Choose the display option
uncheck the first 'Show expand/Collapse buttons'
check the 'Classic PivotTable Layout(enables dragging of fields in the grid)
click ok.

This would refine the data. Then, I had just copy and pasted this data in a new tab wherein I had applied the filters to my Total column with values greater than certain percentage.

This did work in my case and hope it helps you too.

score -1 · Answer 7 · answered Apr 17 '16 at 15:09

Use a value filter. Click the dropdown arrow next to your Row Labels and you'll see a choice between Sort A to Z, Label Filters, and Value Filters. Selecting a Greater Than value filter will let you choose which column to use to filter out rows, even if that column has no dropdown arrow itself.

PivotTable's Report Filter using "greater than"

7 Answers7