I have a situation where I want to show projected inventory vs projected demand for various levels of an item hierarchy. The hierarchy has 6 levels, where level 1 is the item itself and level 6 is the broadest category. I want to use this as a way to predict when I will run out of inventory and not be able to meet demand. This is pretty straight forward at the item level, but when I drill up in the hierarchy, it could look like I have plenty of inventory to cover the demand, when in reality, some items at the lower level could be covered, while others are not. I want to be able to know, at any level in the rollup of the hierarchy, if I have any risk within any level of the hierarchy so that I can drill down until I find the items that are truly at risk. Right now, this is only conceptual. I haven't built a data model. Has anyone had a similar issue and figured out if is a way to do this? I'm thinking maybe there is some way to call out a negative variance even if it is buried within a rollup.
