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
Hello,
You should arrange your data in a tabular format, see example in attached file.
If you want the layout as you have built in your table, then that can be achieved using a Pivot Table.
Br,
Anders
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
Hello,
The Pivot Table (PT) only shows the data from the source table, so you need to change in the source and then update the PT to get the new values. So you can not change the data in the PT, it needs to changed in the source table.
Br,
Anders
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
Hello,
The only solution I know of being reliable enough is to add numbers to the text you want to sort by. See attached file for an example.
Br,
Anders
OK got it. Thanks again for all your kind help.
Graham
