Dynamic Percentage of Parent Switch Order
Hi all, I am trying to do a percentage of parent calculation which is fine but I am using field parameters so users can dynamically change the row selections in a matrix. If the rows are selected in a specific order then the calculation works fine but if they are selected out of order with the switch calculation then the wrong result is returned. I created a dummy table to represent my issue where there are 3 categories, brand, group and country. DAX formula is: "%Share = VAR _brand = CALCULATE([SUMVALUE],ALLSELECTED('Table'[Brand])) VAR _country = CALCULATE([SUMVALUE],ALLSELECTED('Table'[Country])) VAR _group = CALCULATE([SUMVALUE],ALLSELECTED('Table'[Group])) RETURN SWITCH(TRUE(), ISINSCOPE('Table'[Brand]),DIVIDE([SUMVALUE],_brand), ISINSCOPE('Table'[Group]),DIVIDE([SUMVALUE],_group), ISINSCOPE('Table'[Country]),DIVIDE([SUMVALUE],_country), DIVIDE([SUMVALUE],CALCULATE([SUMVALUE],ALLSELECTED('Table'))) )" If I select country as first level in matrix then group as second level, it works correctly for both levels of the matrix. If I select group then country the second level is wrong. Presumably the group VAR is passing as true first giving sum of all groups in the selected country. So my question is, how can I get it to select the correct VAR regardless of the order the field parameters are selected? Dummy table I created here: Brand Group Country Value Brand1 Group1 GB 37 Brand2 Group1 GB 16 Brand3 Group2 GB 16 Brand4 Group2 GB 21 Brand5 Group2 GB 21 Brand6 Group3 GB 47 Brand7 Group3 GB 41 Brand8 Group3 GB 24 Brand9 Group3 GB 25 Brand1 Group1 FR 21 Brand2 Group1 FR 49 Brand3 Group2 FR 41 Brand4 Group2 FR 22 Brand5 Group2 FR 19 Brand6 Group3 FR 37 Brand7 Group3 FR 27 Brand8 Group3 FR 16 Brand9 Group3 FR 37 Brand1 Group1 DE 9 Brand2 Group1 DE 31 Brand3 Group2 DE 17 Brand4 Group2 DE 21 Brand5 Group2 DE 50 Brand6 Group3 DE 49 Brand7 Group3 DE 33 Brand8 Group3 DE 34 Brand9 Group3 DE 40
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?
