Forum Discussion
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?
1 Reply
- amitchandak
Super User
Anonymous , not very clear, refer if these can help