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

Hi Gospel,

 

Seems like IsInscope is computing the % correctly when I filter at L3 - Phase. 

When I filter at L2 - Package level, IsFiltered computation returns the correct value.

 

Is there a way to combine both IsFiltered and IsInScope to cater both filtering at L3 - Phase and L2 - Package as one single measure?

 

Thanks again.

 

 

 

Cumm Planned IsFiltered(%) =
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 (%)])
)
 
Cumm Planned InScope(%) =
SUMX(All_DATA, IF(ISINSCOPE( 'ALL_DATA'[L4 - Discipline]), [Planned (%)],
IF( ISINSCOPE('ALL_DATA'[L3 - Phase]), [L4 Weightage (%)],
IF( ISINSCOPE('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
jav226 wrote:

 

Seems like IsInscope is computing the % correctly when I filter at L3 - Phase. 

When I filter at L2 - Package level, IsFiltered computation returns the correct value.

 

Is there a way to combine both IsFiltered and IsInScope to cater both filtering at L3 - Phase and L2 - Package as one single measure?

Hi jav226 - I'm seeing the same figures returned by the IsFiltered and IsInScope versions of the calc. It's hard to fix a problem I can't see. Are you able to share the details (or screenshots) of how you have your visuals configured when they return the "incorrect" results?