Reconcilation of values between parent and child -Calculated column
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.
Dax Parent Child Measure
I need help with a measure. I have a multiple parent hierarchy. Thus far PowerPivot only shows me the fully consolidated value. What I would like to see is the consolidated value for a particular level (meaning, all children and the entity itself), and not the value that remains at the top of the hierarchy. To illustrate my problem I have the following hierarchy: C is the child of A (50%) and B (50%). D is the child of C (100%). D has an income of 100€ and C has an income of 50€. So I have a table for my hierarchy (Nodes), one for the income (Fact Data) and one with the unique names (UniqueNames) I loaded them into the datamodel and connected them as followed: Then I added some columns: First I used the ‘Path’ function, ‘pathlength’ and then I used a lookup-function to create the Level Columns. With these levels I created a hierarchy in the data model: Then I added 4 measures: Total_Fact_Income:=SUM(Fact_Data[Income]) Total_Income:=CALCULATE([Total_Fact_Income];Nodes) PctOwnership:=SUM(Nodes[OwnershipPct]) Consolidated:=SUMX(Nodes;[Total_Income]*[PctOwnership]) When I insert a Pivot table and use the hierarchy for my row and the ‘Consolidated’ measure for my value I get: When fully expanded it shows me 25 for C and 50 for D, when collapsed to C or A it shows 75. So this gives me the consolidated values: When you follow the hierarchy path of A, 75€ will remain when the money is at A. So no matter at which level I am, Excel always show the value that remains when the income is fully consolidated (meaning arrived its end point A or B). I would like to have a measure that does not show me the value for the consolidated income all the way up (here A) , but just to the certain entity (here C). Meaning, in my model so far the consolidated income is 75; I would like to see 150 at level C (own income 50 + income from child 100). Only at the level A I would like to see 75 (150*0,5). This means that you only see the unconsolidated values when fully expanded, When collapsed to C you see the consolidated value to this level (which is simply 100 + 50) and when fully collapsed to A there you see the consolidated value of 75. Can someone help me with this measure?
