Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Shadd307
Frequent Visitor

Creating metric from multiple field that updates on a regular schedule

‎06-10-2022 11:34 AM

I am trying to create a metric called Velocity that is the average completed work over a 6 sprint period for a team. I need to create this at the team level so that it can be used for other reports for example we want to use velocity to predict an estimated remaining number of sprints for a specific team based on their velocity and the remaining work they have identified. 

 

I have attached a view of what the data looks like in our Sprints table. Even if I have to add a new table that stores the teams velocity whatever I need but I have struggled with this now for 2 days. I am hoping for responses if you provide an answer thank you so much I am out next week but will be checking this as soon as I get back. Thank you in advance.

 

Labels:
Message 1 of 4
270 Views
0
1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

‎06-13-2022 07:23 PM

Hi @Shadd307 ，

 

I am not sure if I understood your question correctly. Do you have up to 6 sprint period per team in your data? Then you need to calculate the average number of issues completed in those periods. If so, please create the new table.

 

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[team_name],
    "Velocity",
        DIVIDE (
            CALCULATE (
                COUNT ( 'Table'[issues ID] ),
                'Table'[completed before sprint end] = "Complete"
            ),
            DISTINCTCOUNT ( 'Table'[sprint.sequence] )
        )
)

vkkfmsft_0-1655173329908.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
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

updates on a regular schedule.pbix
Message 3 of 4
213 Views
0
3 REPLIES 3
v-kkf-msft
Community Support
Community Support

‎06-13-2022 07:23 PM

Hi @Shadd307 ，

 

I am not sure if I understood your question correctly. Do you have up to 6 sprint period per team in your data? Then you need to calculate the average number of issues completed in those periods. If so, please create the new table.

 

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[team_name],
    "Velocity",
        DIVIDE (
            CALCULATE (
                COUNT ( 'Table'[issues ID] ),
                'Table'[completed before sprint end] = "Complete"
            ),
            DISTINCTCOUNT ( 'Table'[sprint.sequence] )
        )
)

vkkfmsft_0-1655173329908.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

updates on a regular schedule.pbix
Message 3 of 4
214 Views
0
Shadd307
Frequent Visitor
In response to v-kkf-msft

‎10-18-2022 08:50 AM

Thank you so much this worked as expected so very helpful.

Message 4 of 4
192 Views
0
Shadd307
Frequent Visitor

‎06-10-2022 11:45 AM

So I cannot figure out how to add the pbix file, zip file. or xlxs file to my post and when i just copy the few rows from the Sprint tables it exceeds the character limit. I reduced it down hope this helps.

 

issues IDissue_keyprojectProject namesprint.idsprint.namesprint statesprint.endDatesprint.sequencesprint.startDatesprint.completionDateprogram sprint namecompleted before sprint endcommitted/carryovertodays_datecurrent Sprintdone_datestory_pointsteam_name
6696737GDES-116697GDESGDE Project105545GDE Sprint 10.1Closed6/8/20221058035/26/20226/8/2022Sprint 10.1CompleteCommitted05/26/20229Buy
6706740GDES-117428GDESGDE Project105545GDE Sprint 10.1Closed6/8/20221058035/26/20226/8/2022Sprint 10.1CompleteCommitted05/27/202210Buy
6746749GDES-118455GDESGDE Project103404GDE Sprint 9.9Closed5/25/20221058025/12/20225/25/2022Sprint 9.9CompleteCommitted05/12/20223.5Buy
6756752GDES-119553GDESGDE Project103404GDE Sprint 9.9Closed5/25/20221058025/12/20225/25/2022Sprint 9.9CompleteCarryOver05/13/202210Buy
6796762GDES-116697GDESGDE Project102690GDE Sprint 9.8Closed5/11/20221058014/28/20225/11/2022Sprint 9.8CompleteCommitted04/28/20221Buy
6806764GDES-117428GDESGDE Project102690GDE Sprint 9.8Closed5/11/20221058014/28/20225/11/2022Sprint 9.8CompleteCarryOver04/29/20221Buy
6846774GDES-118455GDESGDE Project101068GDE Sprint 9.7Closed4/27/20221058004/14/20224/27/2022Sprint 9.7CompleteCarryOver04/14/20229Buy
6856776GDES-119553GDESGDE Project101068GDE Sprint 9.7Closed4/27/20221058004/14/20224/27/2022Sprint 9.7CompleteCarryOver04/15/20225Buy
6926793GDES-116427GDESGDE Project99446GDE Sprint 9.6Closed4/13/20221057994/1/20224/13/2022Sprint 9.6CompleteCommitted04/4/20227Buy
6936796GDES-117437GDESGDE Project99446GDE Sprint 9.6Closed4/13/20221057994/1/20224/13/2022Sprint 9.6CompleteCarryOver04/5/202213Buy
6956800GDES-119553GDESGDE Project97824GDE Sprint 9.5Closed3/30/20221057983/17/20223/30/2022Sprint 9.5CompleteCommitted03/18/20223Buy
6966803GDES-116698GDESGDE Project97824GDE Sprint 9.5Closed3/30/20221057983/17/20223/30/2022Sprint 9.5CompleteCommitted03/19/20228Buy
6976805GDES-117643GDESGDE Project97824GDE Sprint 9.5Closed3/30/20221057983/17/20223/30/2022Sprint 9.5CompleteCommitted03/20/20228Buy
6694275GDES-117060GDESGDE Project105545GDE Sprint 10.1Closed6/8/20221058035/26/20226/8/2022Sprint 10.1CompleteCarryOver05/28/20225Pricing
6694276GDES-117050GDESGDE Project105545GDE Sprint 10.1Closed6/8/20221058035/26/20226/8/2022Sprint 10.1CompleteCommitted05/29/20221Pricing
6694278GDES-117030GDESGDE Project103404GDE Sprint 9.9Closed5/25/20221058025/12/20225/25/2022Sprint 9.9CompleteCommitted05/12/20224Pricing
6694279GDES-117020GDESGDE Project103404GDE Sprint 9.9Closed5/25/20221058025/12/20225/25/2022Sprint 9.9CompleteCarryOver05/13/20229Pricing
6694286GDES-116951GDESGDE Project102690GDE Sprint 9.8Closed5/11/20221058014/28/20225/11/2022Sprint 9.8CompleteCommitted05/1/202213Pricing
6694287GDES-116941GDESGDE Project102690GDE Sprint 9.8Closed5/11/20221058014/28/20225/11/2022Sprint 9.8CompleteCommitted05/2/202210Pricing
6694288GDES-116932GDESGDE Project101068GDE Sprint 9.7Closed4/27/20221058004/14/20224/27/2022Sprint 9.7CompleteCarryOver04/14/20229Pricing
6694290GDES-116912GDESGDE Project101068GDE Sprint 9.7Closed4/27/20221058004/14/20224/27/2022Sprint 9.7CompleteCommitted04/16/20224Pricing
6694295GDES-116863GDESGDE Project99446GDE Sprint 9.6Closed4/13/20221057994/1/20224/13/2022Sprint 9.6CompleteCommitted04/3/20222Pricing
6694297GDES-116843GDESGDE Project99446GDE Sprint 9.6Closed4/13/20221057994/1/20224/13/2022Sprint 9.6CompleteCarryOver04/5/20225Pricing
6694298GDES-116833GDESGDE Project97824GDE Sprint 9.5Closed3/30/20221057983/17/20223/30/2022Sprint 9.5CompleteCarryOver03/17/20221Pricing
6694299GDES-116823GDESGDE Project97824GDE Sprint 9.5Closed3/30/20221057983/17/20223/30/2022Sprint 9.5CompleteCommitted03/18/20221Pricing
6694301GDES-116803GDESGDE Project97824GDE Sprint 9.5Closed3/30/20221057983/17/20223/30/2022Sprint 9.5CompleteCommitted03/20/20229Pricing
Message 2 of 4
266 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All