Forum Discussion

jav226's avatar
jav226
Frequent Visitor
7 years ago
Solved

Computing rollup weighted sum

Seeking assistance from Power BI experts.   I'm trying to compute the overall progress of Program 1, which is sum of each individual Project Progress, based on the weightage assigned for each Proje...
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    So based on your new requirement to apply the weightings differently at different levels you might be able to do something like the following which just dynamically changes the factor inside the SUMX based on which columns are filtered

     

    SUMX( table1, IF(ISFILTERED( table1[Project]), 1,  -- at the project level multiply by 1
        IF( ISFILTERED(Program], [Project Weightage],  -- at the program level apply the project weight
        [Program Weightage] * [Project Weightage])   -- at the total level apply program and project weighting
    * [Progress])

jav226's avatar
jav226
Frequent Visitor
7 years ago

In Pic01 below, when I'm not slicing any Project, the overall progress of 0.82% at the bar chart is correct.

Pic01

But at Pic02 below, when I slice to Project A and Project E, the Overall Progress in bar chart becomes 14.31%. The expected progress should be 0.64% [ Project A (8.14% * 8.80% * 36%) + Project E (6.17% * 9.60% * 64%)].

 

Pic02

My DAX script as following:-
 
Cumm Planned (%) =
SUMX(All_DATA, IF(ISFILTERED( 'ALL_DATA'[L4 - Discipline]), [Planned (%)],
IF( ISFILTERED('ALL_DATA'[L3 - Phase]), [L4 Weightage (%)], IF( ISFILTERED('ALL_DATA'[L2 - Package]), [L3 Weightage (%)] * [L4 Weightage (%)],
[L2 EPCC Weightage (%)] * [L3 Weightage (%)] *[L4 Weightage (%)])) * [Planned (%)])
)
d_gosbell's avatar
d_gosbell
Super User
7 years ago

Hmm, I was just about to reply back saying that you might just have to create 3 separate measures, then I remembered that PowerBI added a new ISINSCOPE function in Nov last year.

 

You'll have to adjust the column names in the code below as I used the sample table you posted earlier to test this code, but I think this might work for both your tables and charts.

 

Cumm Planned (%) 2 = SUMX( ALL_DATA, IF(ISINSCOPE( ALL_DATA[Project]) , 1,  -- at the project level multiply by 1
    IF( ISINSCOPE(ALL_DATA[Program]) , [Project Weightage (%)] ,  -- at the program level apply the project weight
    [Program Weightage (%)] * [Project Weightage (%)]))   -- at the total level apply program and project weighting
* [Project Progress (%)])