Forum Discussion
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 codes are linked to each other through a secondary hierarchical structure. Meaning that if two codes are listed that are linked, the value for the top hierarchical code is to be used and not the second.
Please see an example of the hierarchy structure:
| Value of Code | Code list | Secondary Hierarch |
| 5 | 8 | 9,10,11,12 |
| 4 | 9 | 10,11,12 |
| 3 | 10 | 11,12 |
| 2 | 11 | 12 |
| 1 | 12 | |
| 5 | 46 | 48 |
| 2 | 48 |
To help further I have three examples and what the solution should be for each.
Example 1: Codes 8,10,46,48 are listed. The calculated value should = 10
Example 2: Codes 10,11,12 are listed, the calculated value should = 3
Example 3: Codes 12,48 are listed, the calculated value should = 3
8 Replies
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)
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,
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.
