2

I am tracking a daily compliance percentage for activities. I want to trend the monthly average compliance for the 10 tests with the lowest compliance total. I am having issues trying to figure out the best way to do this since I am unable to filter by the grand total. Attached is a picture of what the pivot table looks like.

enter image description here

enter image description here

7
  • yes, this is how pivot tables in Excel works. If you need to filter for them, then you need to calculate it in a new column in your source data, then use this new column as a row label in the pivot table. Feb 15, 2016 at 15:44
  • Each test is a column in the data table. Two times a day (AM and PM Shift) I update 5 lines of compliance percentages (one for each production line). There are currently 100 columns on the data worksheet. If I add another column for each test, then this workbook will be VERY massive. I am looking for ideas on how to do this without adding more columns to that table.
    – K.Mack
    Feb 15, 2016 at 18:17
  • If you could post a few sample data, that would help to understand your problem. Feb 15, 2016 at 18:29
  • 1
    I ended up creating a new table (beside the pivot) that is indexed by cell so even if the order of the pivot changes, the top 20 rows will be what is copied over. Then I created the chart from the new table. Thanks for helping!
    – K.Mack
    Feb 16, 2016 at 12:50
  • could you please post your solution as an answer? That would help also others to learn from your case. Feb 16, 2016 at 15:08

2 Answers 2

0

I ended up creating a new table (beside the pivot) that is indexed by cell so even if the order of the pivot changes, the top 20 rows will be what is copied over. Then I created the chart from the new table. Thanks for helping!

enter image description here

0

You can use 'Value filters' Value Filters command from the menu in the Column Labels field

https://www.accountingweb.com/technology/excel/filtering-grand-total-amounts-within-excel-pivot-tables

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .