Using OData query, how to get the 'Progress by all Work Items' rollup column value for a Project?

[VivekGupta]

Hi,

Using Power BI Desktop Tool, I want to show the 'Progress by all Work Items' rollup column value for a given Project Work Item Type in Power BI Report using OData query.

Please let me know the solution for this issue ASAP.

Thanks.

[v-robertq-msft]

Hi, @VivekGupta 

I’ve pasted your M query in my advanced editor, but I get an error. Would you like to check again?

To get better assiatance, I suggest you to post some sample data in table form or pbix file(without sensitive data) and your expected result.

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@VivekGupta ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[VivekGupta]

('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&... 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.