Percent complete by category

[briodan]

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%

[Anonymous · Accepted 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] )
)

[Anonymous]

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	?

[v-yuezhe-msft · Accepted solution]

@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

[Anonymous · Accepted 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]

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.