hi all,
I have the following kind of data table in excel:
Id | Item | SubItem
The SubItem column can contain values ranging from 'A' to 'F' The Item column contains two items: 'Cat' and 'Dog'
There is a dependency between Item and SubItem. For our example, let's assume that all rows where Item = 'Dog' can only have 'A','B','C' as SubItems. On the other hand, if a row contains the entity 'Cat' in column Item, SubItem is restricted to 'D','E','F'.
So far so good.
Now I create a pivot table using Item and SubItem attributes and a count on Item labeled as Total. I choose to show all items even if it has no data. Obviously what happens is that for every Item, all possible SubItem values are shown together with the number of occurrences. But since I know that there are additional dependencies between Item and SubItem, I would like the pivot table to show only the proper SubItems for each particular Item!
How can I achieve that without manually to hide items? Is there a way to model the dependencies?
Thanks a lot!
Cheers Christoph