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.
- VivekGupta5 years agoNew Member
('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.