Forum Discussion
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 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% |
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])
12 Replies
you should be able to do this with a measure like the following:
SUMX( <table name>, [Weightage] * [Progress])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% 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])
