Forum Discussion
Hierarchy Setup Help
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.
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!
