
Active Member

June 18, 2021

I have a table that contains three levels of data - effectively "category", "sub category" and "item". Something like this:
Row 1: Category 1
Row 2: Sub category 1.1
Row 3: Data item 1
Row 4: Data item 2
Row 5: Sub Category 1.2
Row 6: Data item 3
Row 7: Data item 4
Row 8: Category 2
Row 9: Sub category 2.1
Row 10: Data item 5
Row 11: Data item 6
Row 12: Data item 7
Each row has columns such as "Status", "Owner" "Approver" etc (which only apply to the individual data items) and I have slicers so that I can easily filter on such columns. However, because the category and sub category rows do not have this data completed The category and sub-category rows associated with the filtered data items are lost.
Is there a way to show "hierarchical" or "parent" rows so that, for example, if a filter selects data item 3 in row 6 it also displays Row 1 and Row 5 in the example above. I realise this may be a coding requirement rather than a filtering requirement but I'm not sure where to start).
I have attached some example data to make it clearer. Note the three columns called Team, Family and Item - "Item" is always zero if a category or sub-category, all data items have a value >0 in "item" - not sure if this helps.
If anyone has any ideas please let me know...
Thanks, Graham

VIP

Trusted Members

December 7, 2016


Active Member

June 18, 2021

Thanks Anders that's a great help. I really appreciate that you have gone to the trouble of sending me back a revised example with your suggestion - so much easier than trying to figure it out from the text description... I have a follow up question which I think is something that can't be done, but I'd like to check.
Is there any way that the original data in the table can be updated by updating the pivot table?
For example, the pivot table is a nice friendly way to discuss things such as dates, if a date needs to change is there any way that I can change it in the pivot table during a live discussion - or must I go back to the original table and change it there?
Thanks again for your help,
Graham

VIP

Trusted Members

December 7, 2016


Active Member

June 18, 2021

Thanks again Anders, it's as I thought.
Sorry to ask but I have one more question:
2) The order of items change in the PT - I see it happens in your example also e.g. rows 25 & 26 in your pivot table are reversed from the source (rows 3 & 4 in the table). Is it possible to ensure 100% same order as source?
Thanks in anticipation,
Graham
1 Guest(s)
