Reconcilation of values between parent and child -Calculated column

InsHunter — Tue, 01 Aug 2023 12:59:41 GMT

Hi,
Request your support on formulating the correct DAX formula for caclulated column which calculates the value of sum of the children of that Node. There are more than five levels of parent ,child relation ship.Reconciliation need to happen for all parents with their immediate next level children. The generated table is as below

My dax formula for the calculated column "Units_Expected" is not giving values correctly as its aggregating totally. In this sample only three levels are shown but acutal data has more levels. I request a generic formula for any number of levels.

Units_Expected =

if(

Units_Reconciliation_Table[Node Type]="level1",

sumx(

FILTER(Units_Reconciliation_Table,PATHITEM(Units_Reconciliation_Table[Entity Path],2,INTEGER) =Units_Reconciliation_Table[Child Node ID]),

Units_Reconciliation_Table[Units_Actuals]),

(

if(Units_Reconciliation_Table[Node Type]="level2",

sumx(

FILTER(Units_Reconciliation_Table,

AND(

PATHITEM(Units_Reconciliation_Table[Entity Path],3,INTEGER) =Units_Reconciliation_Table[Child Node ID],

PATHITEM(Units_Reconciliation_Table[Entity Path],2,INTEGER) =Units_Reconciliation_Table[Immediate_Parent])),

Units_Reconciliation_Table[Units_Actuals]),

if(Units_Reconciliation_Table[Node Type]="level3",

sumx(

FILTER(Units_Reconciliation_Table,PATHITEM(Units_Reconciliation_Table[Entity Path],4,INTEGER) =Units_Reconciliation_Table[Child Node ID]),

Units_Reconciliation_Table[Units_Actuals]),

if(Units_Reconciliation_Table[Node Type]="level4",

sumx(

FILTER(Units_Reconciliation_Table,PATHITEM(Units_Reconciliation_Table[Entity Path],5,INTEGER) =Units_Reconciliation_Table[Child Node ID]),

Units_Reconciliation_Table[Units_Actuals])

)

)))

Appreciate your help . Thanks in advance.

Re: Reconcilation of values between parent and child -Calculated column

InsHunter — Tue, 01 Aug 2023 17:37:06 GMT

Could simplify the DAX and achieve the objective and it can work for any number of levels.Sorry for over complicating the things initally.

Units_Expected =

VAR Node_ID=[Child Node ID]

RETURN

sumx(

FILTER(Units_Reconciliation_Table,

(Units_Reconciliation_Table[Immediate_Parent] =Node_ID)),

Units_Reconciliation_Table[Units_Actuals])

Re: Reconcilation of values between parent and child -Calculated column

InsHunter — Tue, 01 Aug 2023 17:42:32 GMT

Pl see the outcome of the calculated columns in the generated table

topic Re: Reconcilation of values between parent and child -Calculated column in DAX Commands and Tips

Reconcilation of values between parent and child -Calculated column

Re: Reconcilation of values between parent and child -Calculated column

Re: Reconcilation of values between parent and child -Calculated column