Forum Discussion
Computing rollup weighted sum
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])
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])
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.
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])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.
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?