Change Measure / Group when drill down on a bar chart

[anthony28852]

Let say I have a table like this.

Continent	Country	City	Revenue (M)
America	USA	New York	1000
America	USA	New York	2000
America	USA	Texas	1500
America	USA	Texas	2000
America	Mexico	Guadalajara	1500
America	Mexico	Guadalajara	1500
America	Mexico	Puebla	2000
America	Mexico	Puebla	1500
Asia	Indonesia	Jakarta	1000
Asia	Indonesia	Jakarta	1500
Asia	Indonesia	Bandung	2000
Asia	Indonesia	Bandung	2500
Asia	Malaysia	Kuala Lumpur	1500
Asia	Malaysia	Kuala Lumpur	2000
Asia	Malaysia	Johor Bahru	1500
Asia	Malaysia	Johor Bahru	1000

I want to have a drill down stacked-bar chart that represent this.

On the first level, the category should be Continent and the Group should be Country with the Revenue as the Value.

After drilling down, the category should be Country and the Group should be City.

I can achieve the category changing by using hierarchy, but how do I change the grouping everytime I drilldown? 

Thank you!

Anthony Steven

[Renato_sas]

Hi @anthony28852,

The way I've found you can do that is by implementing your own drill down capability, so you know in wich level of the hierarchy you are and use that information to modify the data item assigned to the group role. It's not ideal, but it worked.

In the example below, I have a "hiearchy" with 4 levels (Continent / Country / State / City) so I've added 3 drop-down lists to perform the navigation, one for each level of the hierarchy (we don't need a drop-down for the last level of the hierarchy). Each one of the drop-down controls has a parameter assigned to them called _continent, _country, and _state respectively.

Those parameters were used to derive two calculated items: Geo and Geo Subdivision. Geo contains the values of the current level of the hierarchy, and Geo Subdivision contains the next level. For example, if nothing is selected in the drop-down controls, it means we are at the highest level of the hierarchy (so Geo works as continent and Geo Subdivision works as country). If a continent is selected, Geo will be displaying countries and Geo Subdivision will contain the states (and so on...)

Expression for Geo:

Expression for Geo Subdivision:

I've then assigned Geo to the Category role in the Bar Chart and Geo Subdivision in the Group role, as well as Revenue as the measure.

Finally, the interactions were defined as below. Observe that this gives a cascading prompt from continent to country to state.

This is the final result (initially at the continent level):

Choose North America as the continent:

Choose United States as the country:

Choose Texas as the state:

To help identify which level of the hierarch we are looking at, I've added a Button Bar at the top left of the report and assigned a calculated item to it called Geo Level, defined as follows:

Best,

Renato