I am trying to build a dashboard that allows managers to compare the performance of one level of a hierarchy against another level("Region to System" or "Store to Region").
The attached workbook works, but I don't think it is completely intuitive to the end user and requires a lot of clicks. I also don't think it will work well with 500 stores and 10 regions.
In the attached, there are 3 different parameters for each line.
If Level of Detail is "System", the Region and Store parameters have no influence for the specific line.
If the Level of Detail is "Region" or "Store", the line is based on the selection in the related Region or Store parameter.
Ideally, I would like the second parameter option to change based on the first.
If a user selects System for the first level of detail, I'd like the option for the second line to be limited to Regions.
If the user selects Region for the first level of detail, I'd like the user to select a Region for the first line and then have the options for the second line be limited to the Stores under the Selected Region.
I don't think there is a way to accomplish this with parameters or filters, so I'm looking for other suggested alternatives to compare between the hierarchical levels for a large number of regions and stores.