Forum Discussion
Using OData query, how to get the 'Progress by all Work Items' rollup column value for a Project?
VivekGupta ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.
('Progress by all Work Items' rollup column: https://devblogs.microsoft.com/devops/track-the-progress-of-work-using-rollup-columns/)
Referred to https://docs.microsoft.com/en-us/azure/devops/report/powerbi/sample-boards-rollup?view=azure-devops&tabs=powerbi example to get sum of Original Estimate and Completed Work.
Power BI Query Used:
let
Source = OData.Feed("https://xxxx.xx.com/xxx/_odata/v3.0-preview/WorkItems?$filter=WorkItemType eq 'Project'and Descendants/any()&$select=WorkItemId,WorkItemType,Title,State,ChangedDate,ParentWorkItemId,Custom_ProjectStartDate,Custom_ProjectCompletionDate&$expand=AssignedTo($select=UserName),Descendants($apply=filter(WorkItemType eq 'Task' OR WorkItemType eq 'Project Task')/aggregate(OriginalEstimate with sum as TotalOriginalEstimate,CompletedWork with sum as TotalCompletedWork,RemainingWork with sum as TotalRemainingWork))", null, [Implementation="2.0"]),
#"Expanded Descendants" = Table.ExpandTableColumn(Source, "Descendants", {"TotalOriginalEstimate", "TotalCompletedWork", "TotalRemainingWork"}, {"Descendants.TotalOriginalEstimate", "Descendants.TotalCompletedWork", "Descendants.TotalRemainingWork"})
in
#"Expanded Descendants"
Used Formula: ((TotalCompletedWork/TotalOriginalEstimate) * 100) to get the 'Progress %' for a given Project.
However, the Progress % is not matching correctly with 'Progress by all Work Items' rollup column value for the different Projects.