Create geographical hierarchy

Hey ys034 ,

Others may have different ideas. But if I were tackling this, I would see if the datasource could suppress the subtotal rows AND you don't have an attribute table somewhere that ties countries to the World > Region > Subregion hierarchy. If not, I would use Power Query to delete the aggregate rows in the data. Then I would add a columns for the region evaluation.

1. Add Custom Column that just sets the entry to "World" If you really do need this level

2. Add a Custom Column or Calculated Column to add the Region (EU)

Table.AddColumn(#"Changed Type1", "Hierarchy Region", each if List.Contains( {"France", "Germany", "Russia", "Turkey", "Ukraine", "United Kingdom"}, [Region]) then "EU" else if List.Contains( {"United States", "Canada", "Mexico"},[Region]) then "NA" else null, type text)

3. Add one for the Sub-Region that would generate the column for the sub-region EEU or WEU

Table.AddColumn(#"Region 1", "Hierarchy Sub Region", each if List.Contains( {"Russia", "Turkey", "Ukraine"}, [Region]) then "EEU" else if List.Contains( {"France", "Germany", "United Kingdom"}, [Region]) then "WEU" else if List.Contains( {"United States", "Canada", "Mexico"},[Region]) then "NA" else null, type text)

In my example above, I created an entry for North America so I could highlight the script for additional regions.

Here's what it would look like in Power Query with just the data you posted above:

Then close and apply and you can create your hierarchy in the Field well. And that will let you turn on and off the subtotals at the visual level.

Hope this helps!