Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 :
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% |
Solved! Go to Solution.
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.
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 :
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% |
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.
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 ?
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.
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 |
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.
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 |
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |