1 Reply Latest reply: Dec 3, 2015 1:55 PM by Mike Grattan RSS

    How to filter pivot table on aggregate total of a measure?

    Mike Grattan

      I have a pivot table that shows rows for employees and columns for years; the measure is "sum([number of incidents])".  I'd like to be able to filter the table so it shows only employees with a number of incidents greater than <a number selected by the user>.  It would be great, for example, if I could embed a slider in the Sheet with numbers from 1-10 and let the user pick a number; then the pivot table is filtered to show only employees who had at least that number of incidents.  In the screen grab below you can see that we have multiple years in the table.  I would want to first have a total for all years, and then be able to filter by that total.

       

      10.28.2015-14.31.png

        • Re: How to filter pivot table on aggregate total of a measure?
          Mike Grattan

          I figured out a workable solution.

           

          In the pivot table, I turned on "Show Totals" in the Year column in the Data section.  In the Sorting section, I applied a Descending sort order to the Employee row, based on the sum of Incident Count.  I then added some conditional formatting to the Incident Count in the Background color expression formula:

           

          if( sum(INCIDENTCOUNT) < 2, Green(),if( sum(INCIDENTCOUNT) < 5 ,Yellow(), if(sum(INCIDENTCOUNT) >=5, Red())))

           

          This resulted in the following pivot table appearance:

           

          12.03.2015-10.53.png