Forum Discussion
Adding a summary column for parent at multlevel Hierarchy
- 1 year ago
Hi k_rahul_g - Ensure that your BaseData table and the Parent_Child table have the necessary relationship based on the WorkItemID and ParentID. This will help DAX understand the hierarchy.
Recursive Summation Using a Measure the Effort Esitmate across all child work items:Total Effort Recursive =
VAR CurrentWorkItem = MAX(BaseData[WorkItemID])
VAR ChildEfforts =
CALCULATE (
SUMX (
FILTER (
BaseData,
BaseData[ParentID] = CurrentWorkItem
),
[Effort Estimate]
)
)
VAR RecursiveChildEfforts =
CALCULATE (
SUMX (
FILTER (
Parent_Child,
Parent_Child[Parent ID] = CurrentWorkItem
),
[Total Effort Recursive]
)
)
RETURN [Effort Estimate] + ChildEfforts + RecursiveChildEfforts
Hi k_rahul_g - Ensure that your BaseData table and the Parent_Child table have the necessary relationship based on the WorkItemID and ParentID. This will help DAX understand the hierarchy.
Recursive Summation Using a Measure the Effort Esitmate across all child work items:
Total Effort Recursive =
VAR CurrentWorkItem = MAX(BaseData[WorkItemID])
VAR ChildEfforts =
CALCULATE (
SUMX (
FILTER (
BaseData,
BaseData[ParentID] = CurrentWorkItem
),
[Effort Estimate]
)
)
VAR RecursiveChildEfforts =
CALCULATE (
SUMX (
FILTER (
Parent_Child,
Parent_Child[Parent ID] = CurrentWorkItem
),
[Total Effort Recursive]
)
)
RETURN [Effort Estimate] + ChildEfforts + RecursiveChildEfforts