Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 diversifi...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

     

    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

theov's avatar
theov
Advocate III
4 years ago

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