Hierarchy Setup Help

[jsuttmann]

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 

[HotChilli · Accepted solution]

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.

[jsuttmann]

@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!