Forum Discussion

VivekGupta's avatar
VivekGupta
New Member
5 years ago

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.

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      VivekGupta
      New Member
      5 years ago

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

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.