Forum Discussion

rhcentennialh's avatar
rhcentennialh
Helper II
7 years ago

Hierarchical Formula Calculation

I need to develop a formula that returns the sum of values based on a hierarchical structure. My calculation will look at a list of codes, each code has a value attributed to it. In addition, some co...
Need Help
rhcentennialh's avatar
rhcentennialh
Helper II
7 years ago

Just to be more clear on the examples and how the secondary hierarchy should work.

 

Example 1: Codes 8,10,46,48 are listed. The calculated value should =  10 (Codes: 8+46)

Example 2: Codes 10,11,12 are listed, the calculated value should = 3 (Codes: 10)

Example 3: Codes 12,48 are listed, the calculated value should = 3 (Codes: 12+48)

 

 

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

    Hi rhcentennialh 

    You may try below measures.Attached sample file for your reference.

    min_list = MINX(FILTER(ALLSELECTED(Code),Code[Hierarchy]=MAX(Code[Hierarchy])),Code[Code list])
    Codes = IF(ISBLANK(SUM(Code[Secondary Hierarch .1])),SUM(Code[Value of Code]),CALCULATE(SUM(Code[Value of Code]),FILTER(Code,Code[Code list]=[min_list])))

    Regards,

    • rhcentennialh's avatar
      rhcentennialh
      Helper II
      7 years ago

      Thank you, however I need the formula to be more dynamic.

       

      I will have a list of thousands of individuals each with a unique set of codes. I would like the formula to auto calculate the sum rather than having to select the codes individually in the selection box you provided.

      • rhcentennialh's avatar
        rhcentennialh
        Helper II
        7 years ago

        After looking at your file more closely i belive what you did is correct, would you be able to provide addtional steps on how you created these formulas. I am having trouble following.

         

        Thank you!