Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

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
IDWork Item TypeTitleState
1FeatureFeature 1Dev
2User StoryMVP 01 Common Backlog EstablishmentDone
8User StoryMVP 02 Code is Peer ReviewedDone
9User StoryMVP 03 Business Owner(s) engaged in prioritizationReady
10User StoryMVP 04 Release Risks Accepted by Product OwnerTest
11FeatureFeature 2Ready
12User StoryMVP 01 Common Backlog EstablishmentDone
18User StoryMVP 02 Code is Peer ReviewedDone
19User StoryMVP 03 Business Owner(s) engaged in prioritizationDone
20User StoryMVP 04 Release Risks Accepted by Product OwnerReady

 

Result that i would like to see

 

TitleRankSizeTarget dateState%Completed
Feature 12s1/1/2023Dev50%
Feature 25xl11/23/2022Ready75%
1 ACCEPTED 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
)

vzhangti_0-1657615515528.png

 

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.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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
IDWork Item TypeTitleState
1FeatureFeature 1Dev
2User StoryMVP 01 Common Backlog EstablishmentDone
8User StoryMVP 02 Code is Peer ReviewedDone
9User StoryMVP 03 Business Owner(s) engaged in prioritizationReady
10User StoryMVP 04 Release Risks Accepted by Product OwnerTest
11FeatureFeature 2Ready
12User StoryMVP 01 Common Backlog EstablishmentDone
18User StoryMVP 02 Code is Peer ReviewedDone
19User StoryMVP 03 Business Owner(s) engaged in prioritizationDone
20User StoryMVP 04 Release Risks Accepted by Product OwnerReady

 

Result that i would like to see

 

TitleRankSizeTarget dateState%Completed
Feature 12s1/1/2023Dev50%
Feature 25xl11/23/2022Ready75%
v-zhangti
Community Support
Community Support

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" )
    )
)

vzhangti_0-1657503953625.png

%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
)

vzhangti_1-1657504011215.png

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.

 

Anonymous
Not applicable

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?

vzhangti_0-1657525368033.png

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.

 

Anonymous
Not applicable

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

 

FeatureUser StoryParent IDState
Feature 1  Done
 User Story959585Done
 User Story 1959585Done
Feature 2  Done
 User Story 2860033Done
 User Story 3860033Done
 User Story 4860033Done
 User Story 5860033Done
Fearure 3  Done
 User Story 6884930Done
 User Story 7884930Done
 User Story 8884930Done

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
)

vzhangti_0-1657615515528.png

 

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.

Anonymous
Not applicable

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:

TitleWorkItem TypeWorkItemIDParentIDState
SS and MRCFeature959585 Test
LED breath during SS and MRCStory959587959585Ready
DCR approvalTask959594959587Done
focus testTask959590959587Done
POC approvalTask959592959587Dev
Test PlanTask959589959587Done
AMD FeatureFeature884930 Done
Project 1Story913921884930Done
Project 2Story887331884930Done
Project 3Story884775884930Done
Debug BrdFeature960499 Ready
intermittentStory976478960499Done
Status byteStory995998960499Dev
data entryStory964625960499Ready
Team developmentStory982739960499Done
receiving dataStory964364960499Removed
through packetStory964368960499Blocked
using RWEStory964374960499Test
address displayStory886369960499Ready
successfully receivedStory952654960499Demo
successfully received TaskTask952653952654Done
connection is activeStory952652960499Ready
connection is active taskTask952651952652Dev
22PI2 BugsFeature926133 Ready
via FUR.Bug936900926133Done
Hep Inactive TestsBug940835926133Dev
Magnet platformBug947686926133Ready
Notify userBug966115926133Done
Check in Git branchTask960347966115Removed
check in Git trunkTask959717966115Blocked
testing modeStory960979926133Test
Task 1Task960980960979Ready
Task 2Task960981960979Demo
Task 3Task960982960979Done
Task 4Task960983960979Ready
Task 5Task960984960979Dev

 

Expected Results:

FeatureState%Complete
SS and MRCTest60
AMD FeatureDone100
Debug BrdReady25
22PI2 BugsReady23

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors