Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 

Location Hierarchy(a).PNG

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

Location Hierarchy(bad).PNG

SAMPLE of my Report

 

Report 2.PNG

 

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

 

results table.PNG

 

 

3 REPLIES 3
TeigeGao
Solution Sage
Solution Sage

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
Not applicable

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
Not applicable

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.