Multi level parent child hierarchy summarization

[k_rahul_g]

I have a Power BI tabel (Attached) which as workitem, workitem type, Effort (Parent),  Child (of the workitem).  

This table also have multiple level of hierarchy i.e.  a Child would be replicated as a workitem if it has further children and may go on to multiple levels. ( I counted it to go to some times 8 levels). 

The workitem ID (entire row) replicates as many children it has. ( I know it is not the best database table but that is how it has been structured and i dont have the option to change it.)

My problem statement is to summarize the data at the parent level. i.e. if the parent is Defect, then all its children and  grandchildren.. or great grand children's effort is to be summarized at this parent level.  The summarization could be at any level i.e. if defect has story and story has a task child item. I may be required to summarize at story level. 

workItemID	workItemType	childID	estimate
25002	Business Need	25020	22.99139
25002	Business Need	25021	22.99139
25002	Business Need	27999	22.99139
25002	Business Need	28632	22.99139
25021	Defect	1425	2
1400	Defect		10
1425	Task	7799	24
1425	Defect	7825	24
1425	Defect	7826	24
1425	Defect	9893	24
1426	Defect	6092	12
1426	Defect	6096	12
1426	Defect	6099	12
1429	Defect		2
1695	Defect		1
1730	Defect		2
1882	Defect	1905	5
2411	Defect		8
2412	Defect		8
2413	Defect		12
2414	Defect		8
2415	Defect		10
2416	Defect		20
2417	Defect		15
2418	Defect		30
2419	Defect		10

[ImkeF]

Hi @k_rahul_g ,
if you need your table unmodified, you can use this approach: 
Parent-Child Hierarchies with multiple parents in Power BI with Power Query (thebiccountant.com)
It works with multiple parents as well.
Please check file enclosed.

[Syndicate_Admin]

 I tried using th path function and it gives erros that  every workitem (i.e. Parent) should also appear as the child in the table.  

If you examine my Power Query code you will see that this is taken care of.

[Syndicate_Admin]

A child cannot have two parents in a hierarchy  (as opposed to IRL) so ignoring the parent entries for 23156 and 13329. Next we need to fill the gaps for childless parents.  Then you can use the PATH functions for your rollups.

I'll leave the next step to you.

[k_rahul_g]

Your solution, I believe is eliminating some data, which is something I would like to avoid. 

[k_rahul_g]

The table would always have a childless parents. As there would  be node levels. This can not be eliminated. I tried using th path function and it gives erros that  every workitem (i.e. Parent) should also appear as the child in the table.   

My data would not have this established as there would be workitems that are in analysis and have not been divided into further tasks or stories.  

Hence looking for another work arond. 

and while giving sample data i  made that mistake of multiple parents to a child. This is not the scenario in actual data. A child would have 1 parent only.  but a parent would have multiple levels of children. 

[Syndicate_Admin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...