cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
guliver Frequent Visitor
Frequent Visitor

Different measure per drill down level

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:

Drill Down per Country Branch RU.jpgwith 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!

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

Re: Different measure per drill down level

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
v-cherch-msft Super Contributor
Super Contributor

Re: Different measure per drill down level

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
guliver Frequent Visitor
Frequent Visitor

Re: Different measure per drill down level

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.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 261 members 3,169 guests
Please welcome our newest community members: