Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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% |
Solved! Go to Solution.
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
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 | ? |
@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
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] )
)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |