topic %Complete rollup for a feature based on state of a user story in DAX Commands and Tips

%Complete rollup for a feature based on state of a user story

Anonymous — Thu, 07 Jul 2022 12:32:41 GMT

Hi Team,

I am having difficulty in displaying %Complete rollup for a feature based on state of a user story. I am using followoing DAX query but when i want to diplay only features in a table and their %Complete it displayes inaacurate %Complete.

Measure that i am using :

Pecent complete = DIVIDE(CALCULATE(COUNTA('data'[State]),FILTER('data',OR('data'[State]="Done",'data'[State]="Removed")) ),COUNTA('data'[State]))+0

Sample data

ID	Work Item Type	Title	State
1	Feature	Feature 1	Dev
2	User Story	MVP 01 Common Backlog Establishment	Done
8	User Story	MVP 02 Code is Peer Reviewed	Done
9	User Story	MVP 03 Business Owner(s) engaged in prioritization	Ready
10	User Story	MVP 04 Release Risks Accepted by Product Owner	Test
11	Feature	Feature 2	Ready
12	User Story	MVP 01 Common Backlog Establishment	Done
18	User Story	MVP 02 Code is Peer Reviewed	Done
19	User Story	MVP 03 Business Owner(s) engaged in prioritization	Done
20	User Story	MVP 04 Release Risks Accepted by Product Owner	Ready

Result that i would like to see

Title	Rank	Size	Target date	State	%Completed
Feature 1	2	s	1/1/2023	Dev	50%
Feature 2	5	xl	11/23/2022	Ready	75%

Re: %Complete rollup for a feature based on state of a user story

Anonymous — Wed, 06 Jul 2022 18:05:03 GMT

Hi Team,

Measure that i am using :

Pecent complete = DIVIDE(CALCULATE(COUNTA('data'[State]),FILTER('data',OR('data'[State]="Done",'data'[State]="Removed")) ),COUNTA('data'[State]))+0

Sample data

ID	Work Item Type	Title	State
1	Feature	Feature 1	Dev
2	User Story	MVP 01 Common Backlog Establishment	Done
8	User Story	MVP 02 Code is Peer Reviewed	Done
9	User Story	MVP 03 Business Owner(s) engaged in prioritization	Ready
10	User Story	MVP 04 Release Risks Accepted by Product Owner	Test
11	Feature	Feature 2	Ready
12	User Story	MVP 01 Common Backlog Establishment	Done
18	User Story	MVP 02 Code is Peer Reviewed	Done
19	User Story	MVP 03 Business Owner(s) engaged in prioritization	Done
20	User Story	MVP 04 Release Risks Accepted by Product Owner	Ready

Result that i would like to see

Title	Rank	Size	Target date	State	%Completed
Feature 1	2	s	1/1/2023	Dev	50%
Feature 2	5	xl	11/23/2022	Ready	75%

Re: %Complete rollup for a feature based on state of a user story

v-zhangti — Mon, 11 Jul 2022 01:48:09 GMT

Hi, Anonymous

You need to categorize your sample data, try the following methods.

Column:

Type = IF ( [Title] = "Feature 1", BLANK (), IF ( [ID] < CALCULATE ( MIN ( data[ID] ), FILTER ( 'data', [Title] = "Feature 2" ) ), "Feature 1", IF ( [Title] = "Feature 2", BLANK (), "Feature 2" ) ) )

%Completed = DIVIDE ( CALCULATE ( COUNTA ( 'data'[State] ), FILTER ( 'data', OR ( 'data'[State] = "Done", 'data'[State] = "Removed" ) && [Type] = EARLIER ( 'Table'[Title] ) ) ), CALCULATE ( COUNTA ( 'data'[State] ), FILTER ( data, [Type] = EARLIER ( 'Table'[Title] ) ) ) + 0 )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: %Complete rollup for a feature based on state of a user story

Anonymous — Mon, 11 Jul 2022 07:36:28 GMT

Hi v-zhangti ,

I wont be able to categorize based on title as there will be thousands of features and in furure these might change. The data that was provided is a sample data, is there another way of approach ?

Re: %Complete rollup for a feature based on state of a user story

v-zhangti — Mon, 11 Jul 2022 07:44:53 GMT

Hi, Anonymous

Without classification, the calculated formula cannot distinguish the difference between the two. The result is both 50%. Are there any changes to your example data to other states?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: %Complete rollup for a feature based on state of a user story

Anonymous — Mon, 11 Jul 2022 12:29:24 GMT

Each story has ParentID (Feature ID) can we use that to make the calculation ?

Feature	User Story	Parent ID	State
Feature 1			Done
	User Story	959585	Done
	User Story 1	959585	Done
Feature 2			Done
	User Story 2	860033	Done
	User Story 3	860033	Done
	User Story 4	860033	Done
	User Story 5	860033	Done
Fearure 3			Done
	User Story 6	884930	Done
	User Story 7	884930	Done
	User Story 8	884930	Done

Re: %Complete rollup for a feature based on state of a user story

v-zhangti — Tue, 12 Jul 2022 08:46:17 GMT

Hi, Anonymous

ParentID can be used as a category.

%Completed = DIVIDE ( CALCULATE ( COUNTA ( 'data'[State] ), FILTER ( 'data', OR ( 'data'[State] = "Done", 'data'[State] = "Removed" ) && [Parent ID] = EARLIER ( 'Table'[Parent ID] ) ) ), CALCULATE ( COUNTA ( 'data'[State] ), FILTER ( data, [Parent ID] = EARLIER ( 'Table'[Parent ID] ) ) ) + 0 )

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: %Complete rollup for a feature based on state of a user story

Anonymous — Thu, 14 Jul 2022 14:40:13 GMT

Hi v-zhangti

Sorry for Budding you again. The requirement changed and now they would like to get %Complete based on all levels and their State. I tried ParentID as a category but doent work for this requirementm, can you please help 😞

Example Data:

Title	WorkItem Type	WorkItemID	ParentID	State
SS and MRC	Feature	959585		Test
LED breath during SS and MRC	Story	959587	959585	Ready
DCR approval	Task	959594	959587	Done
focus test	Task	959590	959587	Done
POC approval	Task	959592	959587	Dev
Test Plan	Task	959589	959587	Done
AMD Feature	Feature	884930		Done
Project 1	Story	913921	884930	Done
Project 2	Story	887331	884930	Done
Project 3	Story	884775	884930	Done
Debug Brd	Feature	960499		Ready
intermittent	Story	976478	960499	Done
Status byte	Story	995998	960499	Dev
data entry	Story	964625	960499	Ready
Team development	Story	982739	960499	Done
receiving data	Story	964364	960499	Removed
through packet	Story	964368	960499	Blocked
using RWE	Story	964374	960499	Test
address display	Story	886369	960499	Ready
successfully received	Story	952654	960499	Demo
successfully received Task	Task	952653	952654	Done
connection is active	Story	952652	960499	Ready
connection is active task	Task	952651	952652	Dev
22PI2 Bugs	Feature	926133		Ready
via FUR.	Bug	936900	926133	Done
Hep Inactive Tests	Bug	940835	926133	Dev
Magnet platform	Bug	947686	926133	Ready
Notify user	Bug	966115	926133	Done
Check in Git branch	Task	960347	966115	Removed
check in Git trunk	Task	959717	966115	Blocked
testing mode	Story	960979	926133	Test
Task 1	Task	960980	960979	Ready
Task 2	Task	960981	960979	Demo
Task 3	Task	960982	960979	Done
Task 4	Task	960983	960979	Ready
Task 5	Task	960984	960979	Dev

Expected Results:

Feature	State	%Complete
SS and MRC	Test	60
AMD Feature	Done	100
Debug Brd	Ready	25
22PI2 Bugs	Ready	23