Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
rhcentennialh
Helper II
Helper II

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 CodeCode listSecondary Hierarch 
589,10,11,12
4910,11,12
31011,12
21112
112 
54648
248 

 

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 8
rhcentennialh
Helper II
Helper II

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)

 

 

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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!

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

Example.png

Please see the complete database below. Would you be able to recreate the formula and PowerBI example from this data set? 

 

Thank you again for all of the help over the past few days!

 

 Codes
ValueHierarchy 1Hierarchy 2Hierarchy 3Hierarchy 4Hierarchy 5Hierarchy 6
0.3441     
0.4282     
0.4466     
2.65489101112 
1.0279101112  
0.675101112   
0.3091112    
0.15312     
0.307171819   
0.3071819    
0.10619     
0.55421     
0.26222     
0.21223     
0.91327282980  
0.3812829    
0.15329     
0.24333     
0.30834     
0.31535     
0.43139     
0.42640     
1.3944648    
0.68347     
0.21448     
0.368545556   
0.3685556    
0.36856     
0.60657585960  
0.546585960   
0.3535960    
0.35360     
1.338707172103104169
1.1217172104169  
0.51972169    
1.02673     
0.35474     
0.49175     
0.53376     
0.44177     
0.68678     
0.27779     
0.57580     
1.051828384   
0.4048384    
0.31484     
0.3185     
0.22868788   
0.2198788    
0.14388     
0.27196     
0.27699100    
0.276100     
0.498103104    
0.368104     
1.537106107108161189 
0.401107108    
0.305108     
0.509110111112   
0.335111112    
0.216112     
0.612114115    
0.164115     
0.232122     
0.522124     
0.474134135136137138 
0.474135136137138  
0.068137138    
0.068138     
2.112157158161   
1.153158161    
0.551161     
0.262162     
0.57516680167   
0.143167     
0.508169     
0.406170     
0.249173     
0.609176     
0.855186     
0.581188     
0.567189     

The calculation is working perfectly for all of the codes execept for the highlighted sections (Red). The issue from what I can gather is due to a jump in the hierarchy code(s) and that code(s) not being the next subsequent value in the list. Is there a way around this?

ValueHierarchy 1Hierarchy 2Hierarchy 3Hierarchy 4Hierarchy 5Hierarchy 6
0.3441     
0.4282     
0.4466     
2.65489101112 
1.0279101112  
0.675101112   
0.3091112    
0.15312     
0.307171819   
0.3071819    
0.10619     
0.55421     
0.26222     
0.21223     
0.91327282980  
0.3812829    
0.15329     
0.24333     
0.30834     
0.31535     
0.43139     
0.42640     
1.3944648    
0.21448     
0.68347     
0.368545556   
0.3685556    
0.36856     
0.60657585960  
0.546585960   
0.3535960    
0.35360     
1.338707172103104169
1.1217172104169  
0.51972169    
1.02673     
0.35474     
0.49175     
0.53376     
0.44177     
0.68678     
0.27779     
0.57580     
1.051828384   
0.4048384    
0.31484     
0.3185     
0.22868788   
0.2198788    
0.14388     
0.27196     
0.27699100    
0.276100     
0.498103104    
0.368104     
1.537106107108161189 
0.401107108    
0.305108     
0.509110111112   
0.335111112    
0.216112     
0.612114115    
0.164115     
0.232122     
0.522124     
0.474134135136137138 
0.474135136137138  
0.068137138    
0.068138     
2.112157158161   
1.153158161    
0.551161     
0.262162     
0.57516680167   
0.143167     
0.508169     
0.406170     
0.249173     
0.609176     
0.855186     
0.581188     
0.567189     

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors