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?
