Forum Discussion

rhcentennialh's avatar
rhcentennialh
Icon for Helper II rankHelper 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
Icon for Helper II rankHelper 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
Icon for Microsoft Employee rankMicrosoft 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,