Sum calculated measure in different column - Ragged Parent Child Hierachy

[Anonymous]

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 😅

[v-yangliu-msft · Accepted solution]

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