Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
VivekGupta
New Member

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

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.

3 REPLIES 3
v-robertq-msft
Community Support
Community Support

Hi, @VivekGupta 

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

v-robertq-msft_0-1606894795105.png

 

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
Super User
Super User

@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&... 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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.