Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear all,
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:
Country_Results
Country Result Germany 77 UK 222
Branch_Results
Branch Result Branch A 25 Branch B 60 Branch C 222
ReportingUnit_Results
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
StdAlone_Result_SumX = IF( ISFILTERED(CompanyHierarchy[Reporting Unit]), SUMX(ReportingUnit_Results, ReportingUnit_Results[Result]), IF( ISFILTERED(CompanyHierarchy[Branch]), SUMX(Branch_Results, Branch_Results[Result]), SUMX(Country_Results, Country_Results[Result]) ) )
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!
Solved! Go to Solution.
Hi @guliver
You may check the relationships and use below measure:
Measure = IF ( ISFILTERED ( CompanyHierarchy[Country] ), SUM ( Country_Results[Result] ), IF ( HASONEVALUE ( Branch_Results[Branch] ) && HASONEVALUE ( CompanyHierarchy[Reporting Unit] ) = FALSE (), SUM ( Branch_Results[Result] ), IF ( HASONEVALUE ( CompanyHierarchy[Reporting Unit] ), SUM ( ReportingUnit_Results[Result] ) ) ) )
Regards,
Cherie
Basically we use ISFILTERED for such requirements. However, sometimes your total line might be wrong or empty, in that case you can add HASONEFILTER to detect total line and calculate it differently. Also you might need to have different number formats for each level, in that case you can add FORMAT function. Yet because it is a text function you might have empty lines, so you need to add ISBLANK.
This video also explains this thoroughly 🙂
Hi @guliver
You may check the relationships and use below measure:
Measure = IF ( ISFILTERED ( CompanyHierarchy[Country] ), SUM ( Country_Results[Result] ), IF ( HASONEVALUE ( Branch_Results[Branch] ) && HASONEVALUE ( CompanyHierarchy[Reporting Unit] ) = FALSE (), SUM ( Branch_Results[Result] ), IF ( HASONEVALUE ( CompanyHierarchy[Reporting Unit] ), SUM ( ReportingUnit_Results[Result] ) ) ) )
Regards,
Cherie
Hi Cherie,
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.