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!

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

        Hi rhcentennialh 

        First,Split the columns in query editor.Then create the two columns in the table.

        Max = MAX(MAX(MAX(Code[Secondary Hierarch .1],Code[Secondary Hierarch .2]),Code[Secondary Hierarch .3]),Code[Secondary Hierarch .4])
        Hierarchy = CALCULATE(MAX(Code[Max]),FILTER(Code,Code[Code list]<=EARLIER(Code[Code list])))

        Last,create the below measures to get the codes.

        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,