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

User · Answer

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

User · Answer

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

User · Answer

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  amp  fld Name  amp                          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        gt      amp  tbl 0              Else                 Worksheets  Forecast   Range  ProbSelection     Left sSelection  Len sSelection  - 1              End If          End With

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[excel] PivotTable's Report Filter using "greater than"

Examples related to excel

Examples related to pivot-table