Forum Discussion
Percent complete by category
I need to calculate the percent complete of tasks assigned to users
Here is what my data looks like:
| User | Task | Status |
| a | 1 | start |
| a | 2 | progress |
| a | 3 | progress |
| a | 4 | complete |
| b | 5 | start |
| b | 6 | start |
| b | 7 | progress |
| c | 8 | complete |
| c | 9 | complete |
What i need is to get a report that shows what percent of tasks they have completed from the ones assigned to them.
| User | Percent Complete |
| a | 25% |
| b | 0% |
| c | 100% |
Here is a solution.
It gets more complex if you want to apply FILTER() to the formula so that person b will show up and you do not need to show all categories. If filtering for Complete, person b will not show up. In the FILTER() case, the Measure is hard coded for Complete.
Here is the measure and a snippet below showing my results using your provided data:
% of Status =
COUNT ( data[Status] ) / CALCULATE (
COUNT ( data[Status] ),
ALLEXCEPT ( data, data[User] )
)briodan,
You can create a measure using DAX below.Pecent complete = DIVIDE(CALCULATE(COUNTA(data[Status]),FILTER(data,data[Status]="complete")),COUNTA(data[Status]))+0
For more details, please review attached PBIX file.
Regards,
Lydia
4 Replies
briodan,
You can create a measure using DAX below.Pecent complete = DIVIDE(CALCULATE(COUNTA(data[Status]),FILTER(data,data[Status]="complete")),COUNTA(data[Status]))+0
For more details, please review attached PBIX file.
Regards,
LydiaHere is a solution.
It gets more complex if you want to apply FILTER() to the formula so that person b will show up and you do not need to show all categories. If filtering for Complete, person b will not show up. In the FILTER() case, the Measure is hard coded for Complete.
Here is the measure and a snippet below showing my results using your provided data:
% of Status =
COUNT ( data[Status] ) / CALCULATE (
COUNT ( data[Status] ),
ALLEXCEPT ( data, data[User] )
)Thanks that is really close to what I need.
I need to only display the Complete status with its value, its ok if users with 0%or blank% show up in the list.
So i guess i need the more complex option.
Hello MS,
How can i get the percentage completion of my work items lets say i have this data.ID Work Item Type Title % of Completion 1 Feature Feature 1 2 User Story MVP 01 Common Backlog Establishment 3 Task Establish the Delivery & SRE Team Roster including PO, SM 4 Task Establish a common backlog in AzDO (team level), including access for all team members 5 Task DevSecOps Team kickoff (can be part of first Sprint Planning/other team event) 6 Task Common Backlog - Core/Cloud Teams 7 Task Common Backlog - Project to Product Teams 8 User Story MVP 02 Code is Peer Reviewed 9 User Story MVP 03 Business Owner(s) engaged in prioritization 10 User Story MVP 04 Release Risks Accepted by Product Owner 11 Feature Feature 2 12 User Story MVP 01 Common Backlog Establishment 13 Task Establish the Delivery & SRE Team Roster including PO, SM 14 Task Establish a common backlog in AzDO (team level), including access for all team members 15 Task DevSecOps Team kickoff (can be part of first Sprint Planning/other team event) 16 Task Common Backlog - Core/Cloud Teams 17 Task Common Backlog - Project to Product Teams 18 User Story MVP 02 Code is Peer Reviewed 19 User Story MVP 03 Business Owner(s) engaged in prioritization 20 User Story MVP 04 Release Risks Accepted by Product Owner Expected Results:
ID Work Item Type Title % of Completion 1 Feature Feature 1 ? 11 Feature Feature 2 ?
