I am having a problem with parameters in a hierarchy with (what I hope to be) dependent filters. I have attached a sample workbook with superstore data to illustrate the situation.
The hierarchy is the category hierarchy and my goal is to be able to look at only one displayed value regardless of the level of hierarchy or filter chosen.
Currently, I have two filters and one parameter. The parameter determines the level of hierarchy, the first filter determines which category, and the second filter determines which subcategory of the already determined category (e.g. only relevant values are checked).
The problem I am encountering is this:
- I originally had single value only dropdown filters in place which would allow only one value to be viewed (which is ideal for the dashboard design in question). Unfortunately, when I make the subcategory filter a single value dropdown filter it immediately applies itself to the category value when I have the parameter chosen as category and the value displayed is no longer an aggregate across said category but is instead just the subcategory value displayed with the category header. This results in a correct value and display when parameter is subcategory, but a correct display and incorrect value when parameter is category.
- To alleviate the above problem, I made the subcategory filter multiple value dropdown but then there is the possibility (and probability) that the user will select more than one value which would disrupt the dashboard design. This could (and most certainly would) result in the correct value and display when the parameter is category, but an incorrect display and correct value when parameter is subcategory.
Can anyone shed some light on how to utilize single value filters but only have the subcategory filter in play when the parameter is designated subcategory? I want to create a calculated field but I do not know how to turn a filter on or off in this regard.
Thanks for any and all input!