Different measure per drill down level

[guliver]

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.

• on country level show the results from the Country_Results table,
• on branch level, show the results from the Branch_Results table and
• on reporting unit level show the results from the ReportingUnit_Results table

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!

[theov]

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 🙂

https://www.youtube.com/watch?v=LV9LqcEmgFc

[v-cherch-msft · Accepted 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

[guliver]

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.