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
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,

      • rhcentennialh's avatar
        rhcentennialh
        Helper II
        7 years ago

        Thank you for the further explanation, I do have one last question.

         

        Should the data table look like what is below? This is the real data with many more codes and relationships. Should 80 be represented in the 'Hierarchy" calculation between 1st Hierarchy Codes 27-60?

         

        I ask because when I start calculating total values I don't get the correct answer on certain relationships.