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

The computation of the Progess (%) should be dynamic based on which level is selected.

 

For eg:-
When selected Project A, Progress (%) = 4.8%
When selected Program 1, Progress (%)= = 0.94%
When selected Program 2, Progress (%) = 2.4%
When selected Overall Progress (Program 1 and Program 2), the Progresss (%) = 1.96%

 

I don't think in this case, SUMX( <table name>, [Program Weightage] * [Project Weightage] * [Progress]) works.

d_gosbell's avatar
d_gosbell
Super User
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

    Hi,

     

    I hit another roadblock, hope you can assist again. 

     

    Your DAX works perfectly fine when I display the records in a table.

     

    When I display a chart showing the overall All Programs progress, and start to slice to only Project A and Project B, it sums up the Progress of Project A and Project B which is not correct. Because I'm at overall All Programs level, the expectation is to sum up the progress of Project A and Project B with consideration of the Project and Program weightage. 

    • d_gosbell's avatar
      d_gosbell
      Super User
      7 years ago

      It should not matter if the data is visualized in a table or a chart.

       

      But I don't really understand what your issue is - I can't understand how you can be both at the total level and slicing by a project. Can you maybe share a screenshot or something? 

      • 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 (%)])
        )