Forum Discussion
Percentage of features completed
- I have created powerbi dashboard using azure devops analytics as data source
I have the list of fields from ado into powerbi
WorkItem ID
WorkItem Type
State
Parent Work ItemId
The items are epics, features, story, task which are related by parent work item id
I am looking for pulling out a rollup status and percentage completion at epic and feature level
I have tried to use the blank query in powerbi using the code in below link
I am able to save the query without any syntax error, however the query is not yielding any data. I am not sure how to overcome and get the data source that I can proceed with calculation of percentage
In the meantime is there a simple way to derive this percentage of features or epics rolled up based on their child status using the DAX or query
v-venuppu The solution is as follows
a) Create 2 Copies of the complete dataset as tables which has the list of all features and stores linked using ParentWorkItem ID
b) Rename the tables of dataset as Features and Stories
c) Create one to many relation from Features table by mapping Work Item ID field to Parent Work Item ID in Stories table
d) Create 2 measure - Completed Story Points and Total Story Points in the Features table
e) Completed Story Points - Use SumX to sum all stories that are in completed or done status
f) Total Story Points - Use SumX to sum the story points from all stories
f) Create a measure called % Completion using Completed and Total Story Points
15 Replies
Hi SureshA ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Liu Yang, Thanks
Pls find below sample data and output
Input Data Source
Area Path Work Item Id Title State Work Item Type Story Points Project\Product1\Squad 1 100 Feature A In Progress Feature 40 Project\Product1\Squad 1 101 Story A1 Done Story 10 Project\Product1\Squad 1 102 Story A2 In Progress Story 10 Project\Product1\Squad 1 103 Generic Task A3 Done Generic Task 10 Project\Product1\Squad 1 104 Generic Task A4 In Progress Generic Task 10 Project\Product2\Squad 2 200 Feature B Done Feature 80 Project\Product2\Squad 2 201 Story A1 Done Story 20 Project\Product2\Squad 2 202 Story A2 Done Story 20 Project\Product2\Squad 2 203 Generic Task A3 Done Generic Task 20 Project\Product2\Squad 2 204 Generic Task A4 Done Generic Task 20 Output Data Table Area Path Title State % Complete Project\Product1\Squad 1 Feature A In Progress 50% Project\Product2\Squad 2 Feature B Done 100% Hi SureshA ,
Thank you for reaching out to Microsoft Fabric Community.
Please find the below reference to solve the issue.
Solved: Rollup of features %complete - Microsoft Fabric Community
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
Rama U.
Thanks for following up. I couldnt get the values right after creating the formulas given in the solution
However, i have managed to create the copy of the data set and establish 1 to many relationship from feature to story and able to derive the Features completion based on status and story points defined at stories and tasks
Hi SureshAnanth ,
Great to hear that it's working.Could you please share the solution? It would be really helpful for others in the community who might be facing similar issues and can address them quickly. Also, I would suggest accepting your approach as the solution so that it can benefit others as well.
Thank you.
