Forum Discussion

Shadd307's avatar
Shadd307
Frequent Visitor
4 years ago
Solved

Creating metric from multiple field that updates on a regular schedule

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.

 

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

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

     

    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.

3 Replies

  • Shadd307's avatar
    Shadd307
    Frequent Visitor
    4 years ago

    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
  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

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

     

    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.

    • Shadd307's avatar
      Shadd307
      Frequent Visitor
      3 years ago

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