Forum Discussion

saurabh_kedia_'s avatar
saurabh_kedia_
Microsoft Employee
7 years ago
Solved

Dynamic measure calculation for hierarchy data

Hi Folks,   I'm trying to calculate one measure which can display different values depending on the level of drill down.       Below is the hierarchy: Level1 Name Level2 Name A C B D B E B F B...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Saurabh,

     

    Please download the demo from the attachment then you can check the details.

    1. Establish proper relationships.

    2. Create three measures.

    Not_Started Sum =
IF (
    ISFILTERED ( Table1[Level2 Name] ),
    SUM ( Table3[Not_Started] ),
    SUM ( Table2[Not_Started] )
)

    Inprogress Sum =
IF (
    ISFILTERED ( Table1[Level2 Name] ),
    SUM ( Table3[Inprogress] ),
    SUM ( Table2[Inprogress] )
)

    Completed Sum =
IF (
    ISFILTERED ( Table1[Level2 Name] ),
    SUM ( Table3[Completed] ),
    SUM ( Table2[Completed] )
)

    Dynamic-measure-calculation-for-hierarchy-data

     

    Best Regards,
    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Saurabh,

     

    Did it have the similar structure for the third level like below?

    Level1 Name    Level2 Name     Level3 Name

     

    If so, please try this formula. If not, please provide a sample. The main idea is recognizing the lower level first.

     

    Completed Sum =
IF (
    ISFILTERED ( Table2_1[Level1 Name] ),
    SUM ( Table2_1[Completed] ),
    IF (
        ISFILTERED ( Table1[Level2 Name] ),
        SUM ( Table3[Completed] ),
        SUM ( Table2[Completed] )
    )
)

     

    Best Regards,
    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Saurabh,

 

Please download the demo from the attachment then you can check the details.

1. Establish proper relationships.

2. Create three measures.

Not_Started Sum =
IF (
    ISFILTERED ( Table1[Level2 Name] ),
    SUM ( Table3[Not_Started] ),
    SUM ( Table2[Not_Started] )
)

Inprogress Sum =
IF (
    ISFILTERED ( Table1[Level2 Name] ),
    SUM ( Table3[Inprogress] ),
    SUM ( Table2[Inprogress] )
)

Completed Sum =
IF (
    ISFILTERED ( Table1[Level2 Name] ),
    SUM ( Table3[Completed] ),
    SUM ( Table2[Completed] )
)

Dynamic-measure-calculation-for-hierarchy-data

 

Best Regards,
Dale

  • saurabh_kedia_'s avatar
    saurabh_kedia_
    Microsoft Employee
    7 years ago

    Hi Jiascu,

     

    Thanks a lot for your response.

    It is working well with two levels of hierarchy but I actually needed the same for three levels of hierarchy.

     

    I thought the solution can simply be implemented on to three levels although this was not the case.

    Assuming there is one more table in the hierarchy i.e. above Table 2 namely Table2_1, how can the same be implmented.

    I tried to make a calculation as :

     

    Completed Sum =

     

    IF(ISFILTERED(Table2_1[Level1 Name])=FALSE, SUM(Table2_1[Completed]),IF(ISFILTERED(Table1)=FALSE,SUM(Table1[Completed]),SUM(Table2[Completed]))

     

    Kindly assist on the same, I have searched through some other articles but this is apprently not so straight forward I guess.

     

    Regards,

    Saurabh

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      Hi Saurabh,

       

      Did it have the similar structure for the third level like below?

      Level1 Name    Level2 Name     Level3 Name

       

      If so, please try this formula. If not, please provide a sample. The main idea is recognizing the lower level first.

       

      Completed Sum =
IF (
    ISFILTERED ( Table2_1[Level1 Name] ),
    SUM ( Table2_1[Completed] ),
    IF (
        ISFILTERED ( Table1[Level2 Name] ),
        SUM ( Table3[Completed] ),
        SUM ( Table2[Completed] )
    )
)

       

      Best Regards,
      Dale