I have my data: project activities which are spread over a timeline. The activities are grouped by another column called WBS.
Each activity has different start and finish dates within the timeline.
I calculated the cumulative % planned progress for all the activities to determine the project's planned progress at a selected date within the timeline.
Now I want to determine the % planned progress for each group of activities under a WBS over their individual timeline but this will be achieved when I filter by the WBS or even filter by Activities.
Example: the project overall cumulative % progress is 45%: **bleep** planned units till selected date/overall Units
but WBS_1 under the project is 90% complete
WBS_2 is 70%
WBS_3 is 20%
etc.
1 ACCEPTED SOLUTION
Hi, @SKY-07
You can try the following methods.
Select the first four columns-Unpivot Other columns:
Rename the date column.
overall Units = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[WBS Name],'Table'[Activity Name]),[Spreadsheet Field]="Budgeted Units"))Measure = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[WBS Name],'Table'[Activity Name]),[Spreadsheet Field]="Actual Units"&&[Date]<=MAX('Date'[Date])))Measure % = DIVIDE([Measure],[overall Units])
Is this the result you expect? Please see the attached document. If this fails to solve your problem, please advise what output you would expect based on the data you have provided.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @SKY-07
You can try the following methods.
Select the first four columns-Unpivot Other columns:
Rename the date column.
overall Units = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[WBS Name],'Table'[Activity Name]),[Spreadsheet Field]="Budgeted Units"))Measure = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[WBS Name],'Table'[Activity Name]),[Spreadsheet Field]="Actual Units"&&[Date]<=MAX('Date'[Date])))Measure % = DIVIDE([Measure],[overall Units])Is this the result you expect? Please see the attached document. If this fails to solve your problem, please advise what output you would expect based on the data you have provided.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
