Forum Discussion

k_rahul_g's avatar
k_rahul_g
Frequent Visitor
1 year ago
Solved

Adding a summary column for parent at multlevel Hierarchy

Hi, I have a power BI table  (BaseData) with columns as  WorkItemID, WorkItem Type,  ParentID, Path (created using Path Function) , Effort Esitmate, Effort Spent.    for Simplicity, I have added a...
  • rajendraongole1's avatar
    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