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.
-
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.– Máté JuhászFeb 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.MackFeb 15, 2016 at 18:17
-
If you could post a few sample data, that would help to understand your problem.– Máté JuhászFeb 15, 2016 at 18:29
-
1I 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.MackFeb 16, 2016 at 12:50
-
could you please post your solution as an answer? That would help also others to learn from your case.– Máté JuhászFeb 16, 2016 at 15:08
|
Show 2 more comments
2 Answers
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!
You can use 'Value filters' Value Filters command from the menu in the Column Labels field