Help: 2 source files linked by location to display Parent Child totals

[Anonymous]

I'm a PowerBi novice I have and excel sheet listed with all of my locations listed as a hierarchy with Country- Region-State-City-Neighborhood-Unique location name listed in individual rows. In a separte file, I have cases listed by open and closed cases, case number, and also the Unique location name as the linked category. 

How can I create a table that starts with the Regions and sums up the total for all child locations below it and when you click on it you continue to drill down to see the total Open/Closed cases in each child location below. 

I'm not even sure if this is possible. Another issue I'm having is some location trees only go State- City- Unique Location and some have all 5 step downs so the unique location might be in a different column depending on the state.  

I've tried to search for assistance on this but have not had luck, likely because I do not know the proper terms to search. If someone knows how to link these results to a drawing of the US so its geographically relatively accurate that would be even more amazing. Open for links for directions to link these results, but I'm just lost right now. 

Here is a sample of my location table file 

Assuming locations cannot be in a true Hierarchy like this? (would be cool) 

SAMPLE of my Report

Sample output results if user were to Drill down with Totals rolling up into parent as you drill through locations into child locations. 

[TeigeGao]

Hi @Anonymous ,

PowerBI provides a feature named drill down to meet this requirement, in your scenario, we can create a hierarchy for your Regions and use SUM() function directly, you can choose any visual to display your data.

For more information, please refer to this document: https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill

Best Regrads,

Teige

[Anonymous]

I've reviewed the link you sent but I'm not sure it is answering what I want, but maybe I'm not understanding how to link my location file with the report results file in order for the hierarchy to function with drilldown. 

[Anonymous]

The main issue is the "location" in one file is in one specific column but in the other file it might exist across 5 different columns. IE one location might exist in the location file because the file might be assigned to city state or region. 

I can't figure out how to define that relationship to then work with drilldown methodology.