I'm trying to put together a chart with a drill-down across a company hierarchy (Country -> Branch - > Reporting Unit) where I cannot just sum up the individual results due to diversification effects.
I tried to solve this by introducing different measures depending on the current drill-down level, i.e.
so that the charts would look like this:
with then only showing e.g. Branch A and B if Germany was chosen for drill-down and so on
The results tables look as follows:
Branch A 25
Branch B 60
Branch C 222
Reporting Unit Result
RU 1 10
RU 2 20
RU 3 60
RU 4 70
RU 5 80
RU 6 90
The company hierarchy looks like this
Country Branch Reporting Unit
Germany Branch A RU 1
Germany Branch A RU 2
Germany Branch B RU 3
UK Branch C RU 4
UK Branch C RU 5
UK Branch C RU 6
I tried already to use a dynamic measure as described here: Dynamic measure calculation for hierarchy data
IF( ISFILTERED(CompanyHierarchy[Reporting Unit]),
And I also tried to use HASONEVALUE as described here: How to change the measure when drilling down in bar chart
Measure = IF(HASONEVALUE(Branch_Results[Result]),SUM(Branch_Results[Result]),SUM(Country_Results[Result]))
Both unfortunately without much success...
I already prepared an example .pbix file that you can (hopefully) download from here
Many thanks in advance for your help!
Go to Solution.
You may check the relationships and use below measure:
ISFILTERED ( CompanyHierarchy[Country] ),
SUM ( Country_Results[Result] ),
HASONEVALUE ( Branch_Results[Branch] )
&& HASONEVALUE ( CompanyHierarchy[Reporting Unit] ) = FALSE (),
SUM ( Branch_Results[Result] ),
HASONEVALUE ( CompanyHierarchy[Reporting Unit] ),
SUM ( ReportingUnit_Results[Result] )
Thanks, the solution was to adjust the cross-filter directions to "both" in the relationships, as you suggested.
Your formula didn't work for me, but my own formula worked with the fixed relationships.
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
We spoke with Power BI Super User, Greg Deckler, about his charity work
Continue your learning in our online communities.