cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsuttmann
Helper I
Helper I

Hierarchy Setup Help

I need assistance organizing location data used in a hierarchy in order to build a relationship with a sales data table.  Attached is a simplified version of the report using that data.  The hierarchy is created from a table with 4 columns for the 4 levels of the hierarchy, therefore, there is no single key field for the location selected.  I built a measure, LocSelected, to determine the current location selected in the hierarchy, but I cannot determine a means of filtering the sales data based on a measure.  At each level of the hierarchy, there is sales data, so I need to be able to filter the sales data based on the location selected, regardless of the level of the hierarchy.

 

So either I need to completely rework the location hierarchy data provided to me so that I can create the relationship with the sales data, or I need to determine a way to filter the sales data via the measure without creating the relationship between the tables.  

LocationHierarchyTest.pbix 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

You could remove all the non-building rows from the Sales table and create a relationship between Building in Locations and Location in Sales. (1.m).

This would mean all the Sales data is at the same granularity and once you have the hierarchy in place, powerbi will take care of all the subtotals and totals.

The main visual would use the location hierarchy from Locations and Sales Volume from Sales.

The slicer would use location hierarchy (it could be multi-select)

You would have to have trust in your data being correct at the lowest level and all the Locations set up correctly in order to make the totals reconcile with the original data.

Let me know what you think.

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

You could remove all the non-building rows from the Sales table and create a relationship between Building in Locations and Location in Sales. (1.m).

This would mean all the Sales data is at the same granularity and once you have the hierarchy in place, powerbi will take care of all the subtotals and totals.

The main visual would use the location hierarchy from Locations and Sales Volume from Sales.

The slicer would use location hierarchy (it could be multi-select)

You would have to have trust in your data being correct at the lowest level and all the Locations set up correctly in order to make the totals reconcile with the original data.

Let me know what you think.

@HotChilli I agree with your approach.  The sales data was provided at each level to reduce the live calculations needed by an accompanying PowerApp, so I was trying to use the same to potentially improve performance within the Power BI report.  If I could use the measure that identifies the currently selected location as a filter, I could keep the tables separated (no relationship).  However, I haven't determine a way to use a saved measure in a filter.  I'll use your approach.  Thank you!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.