Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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?