Forum Discussion
Sum calculated measure in different column - Ragged Parent Child Hierachy
Hi Guys,
I am the calculating weight average of my table.
As you can see below. The actual progress is multiplied by the weightage of the activity to get the gain of the child.
Fabri/Assemble Plate Girders @ Cellar Deck = 100% * 0.20 = 20.23.
How can I calculate the sum of all gains under the Fabrication cellar Deck?
The value should be
(20.23+12.96+12.41+8.25+6.74+6.46+3.59+3.22+2.13+1.99+1.69+1.53+1.00) / 100 = 82.20%
Since my data is a ragged hierarchy (many activities at different levels). Below is the formula I use to calculate the weightage.
% weigtage =
VAR BrowseDepth =
[PlanBrowseDepth]
VAR EntityShowRow =
plan[PlanBrowseDepth] <= plan[PlanRowDepth]
var level_1_BLU = CALCULATE([sum_budgeted_labour_unit],REMOVEFILTERS( plan[level_6_name], plan[level_5_name], plan[level_4_name], plan[level_3_name], plan[level_2_name], plan[level_1_name] ))
var level_2_BLU = CALCULATE([sum_budgeted_labour_unit],REMOVEFILTERS( plan[level_6_name], plan[level_5_name], plan[level_4_name], plan[level_3_name], plan[level_2_name] ))
var level_3_BLU = CALCULATE([sum_budgeted_labour_unit],REMOVEFILTERS( plan[level_6_name], plan[level_5_name], plan[level_4_name], plan[level_3_name] ))
var level_4_BLU = CALCULATE([sum_budgeted_labour_unit],REMOVEFILTERS( plan[level_6_name], plan[level_5_name], plan[level_4_name] ))
var level_5_BLU = CALCULATE([sum_budgeted_labour_unit],REMOVEFILTERS( plan[level_6_name], plan[level_5_name] ))
var level_6_BLU = CALCULATE([sum_budgeted_labour_unit],REMOVEFILTERS( plan[level_6_name]))
var val = SWITCH(
BrowseDepth,
1,DIVIDE([Total_Budgeted_Labour_Unit],level_1_BLU),
2,DIVIDE([Total_Budgeted_Labour_Unit],level_2_BLU),
3,DIVIDE([Total_Budgeted_Labour_Unit],level_3_BLU),
4,DIVIDE([Total_Budgeted_Labour_Unit],level_4_BLU),
5,DIVIDE([Total_Budgeted_Labour_Unit],level_5_BLU),
6,DIVIDE([Total_Budgeted_Labour_Unit],level_6_BLU)
)
VAR Result =
IF ( EntityShowRow ,val)
return result
I'm planning to put the sum of the gains in the actual_% columns and below is the formula I have prepared to put the calculations into: [sum of gain]
actual_% =
VAR Val = if(HASONEVALUE(plan[actual]), plan[sum_actual] , [sum of gain])
Var EntityShowRow =
plan[PlanBrowseDepth] <= plan[PlanRowDepth]
VAR Result =
IF ( EntityShowRow, Val )
RETURN
Result
I hope someone can help me with this. Almost 2 weeks no solution and keep try and error 😅
- Anonymous4 years ago
Hi Anonymous ,
Here are the steps you can follow:
1. Create measure.
sum of all gains under the Fabrication cellar Deck = IF( ISINSCOPE('Table'[Level_1_name])&& NOT( ISINSCOPE('Table'[Level_2_name2])), SUMX(FILTER( ALLSELECTED('Table'),'Table'[Level]=MAX('Table'[Level])&&'Table'[Level_1_name]=MAX('Table'[Level_1_name])),[gain]) , IF( ISINSCOPE('Table'[Level_1_name]),[gain], DIVIDE( SUMX(FILTER( ALLSELECTED('Table'),'Table'[Level]=MAX('Table'[Level])&&'Table'[Level_1_name]=MAX('Table'[Level_1_name])),[gain]),100) ))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
- AnonymousNot applicable
Hi Anonymous ,
Here are the steps you can follow:
1. Create measure.
sum of all gains under the Fabrication cellar Deck = IF( ISINSCOPE('Table'[Level_1_name])&& NOT( ISINSCOPE('Table'[Level_2_name2])), SUMX(FILTER( ALLSELECTED('Table'),'Table'[Level]=MAX('Table'[Level])&&'Table'[Level_1_name]=MAX('Table'[Level_1_name])),[gain]) , IF( ISINSCOPE('Table'[Level_1_name]),[gain], DIVIDE( SUMX(FILTER( ALLSELECTED('Table'),'Table'[Level]=MAX('Table'[Level])&&'Table'[Level_1_name]=MAX('Table'[Level_1_name])),[gain]),100) ))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly