Computing rollup weighted sum

[jav226]

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 Project.

Below is the illustration.  Appreciate if someone can share the expression to achieve this computation. Thanks in advanced.

Program	Project	Weightage	Progress	Project Weighted Progress (Weightage x Progress)
Program 1	Project A	8.80%	4.80%	0.42%
Program 1	Project B	28.20%	1.32%	0.37%
Program 1	Project C	52.00%	0.06%	0.03%
Program 1	Project D	11.00%	1.00%	0.11%
Overall Program 1 Progress				0.94%

[d_gosbell]

you should be able to do this with a measure like the following:

SUMX(  <table name>, [Weightage] * [Progress])

[jav226]

Thanks Gosbell for the quick response.

However, SUMX doesn't seem to resolve the problem. 

Mainly is because each level (Overall > Program > Project) has its own weightage. 

Program	Project	Weightage	Progress	Project Weighted Progress (Weightage x Progress)
Program 1	Project A	8.80%	4.80%	0.42%
Program 1	Project B	28.20%	1.32%	0.37%
Program 1	Project C	52.00%	0.06%	0.03%
Program 1	Project D	11.00%	1.00%	0.11%
Overall Program 1 Progress				0.94%

Program	Project	Weightage	Progress	Project Weighted Progress (Weightage x Progress)
Program 2	Project E	20.00%	2.00%	0.400%
Program 2	Project F	20.00%	1.00%	0.200%
Program 2	Project G	30.00%	3.00%	0.900%
Program 2	Project H	30.00%	3.00%	0.900%
Overall Program 2 Progress				2.40%

Program	Program Weightage	Program Weighted Progress (Overall Program Progress x Program Weightage)
Program 1 Progress	30%	0.28%
Program 2 Progress	70%	1.68%
All Programs Progress		1.96%

This is how my fact table looks like:-

Program	Program Weightage (%)	Project	Project Weightage (%)	Project Progress (%)
Program 1	30%	Project A	8.80%	4.80%
Program 1	30%	Project B	28.20%	1.32%
Program 1	30%	Project C	52.00%	0.06%
Program 1	30%	Project D	11.00%	1.00%
Program 2	70%	Project E	20.00%	2.00%
Program 2	70%	Project F	20.00%	1.00%
Program 2	70%	Project G	30.00%	3.00%
Program 2	70%	Project H	30.00%	3.00%

[d_gosbell]

However, SUMX doesn't seem to resolve the problem. 

Mainly is because each level (Overall > Program > Project) has its own weightage. 

---

So this is additional information. We can really only answer based on the information you provided.

But doesn't the same approach still work? Isn't the program just an additional weighting that you need to apply?

eg.

SUMX(  <table name>, [Program Weightage] * [Project Weightage] * [Progress])

[jav226]

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 · Accepted solution]

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]

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]

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]

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]

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

[jav226]

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]

---

@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?

[jav226]

It works! Thanks again.