Forum Discussion

JeevanMallya's avatar
JeevanMallya
Resolver II
2 years ago
Solved

Unique Sprint Story Points Calculation

I am trying to Calculate Sprint Commitmment.There are some stories that are not completed in the same sprint and gets moved to next sprint.In such cases i dont want to add this story points twice. Here in this case if yiu refer Issue Key 68173 gets executed in multiple sprint.So in case if i sum it then it will keep adding the numbers of sprints it appears rather the need is to consider it once (unique).So here i need a power query that will calculate the sum of all story points committed considering the unique Issue Keys.

ISSUE_KEYSPRINT_NAMESPRINT_NUMBERSPRINT_REPORT_STATUSSTORY_POINTS_COMMITTED
54244Sprint 202202COMPLETEDnull
62009Sprint 203203INCOMPLETED0
62009Sprint 204204COMPLETEDnull
65726Sprint 202202INCOMPLETEDnull
65726Sprint 203203INCOMPLETEDnull
65726Sprint 204204REMOVEDnull
66113Sprint 202202COMPLETED0
66198Sprint 203203COMPLETED0
68164Sprint 204204COMPLETED0
68173Sprint 203203INCOMPLETED0
68173Sprint 204204INCOMPLETED5
68173Sprint 205205COMPLETED5
68651Sprint 202202INCOMPLETED5
68651Sprint 203203COMPLETED5
68653Sprint 205205INCOMPLETED3
68799Sprint 202202COMPLETED3
68888Sprint 202202COMPLETED4
69669Sprint 202202COMPLETED0
69825Sprint 202202COMPLETED3
69827Sprint 202202INCOMPLETED4
69827Sprint 203203INCOMPLETED4
69827Sprint 204204COMPLETED4
70191Sprint 205205COMPLETED0
70244Sprint 203203COMPLETED0
70249Sprint 203203COMPLETED0
70448Sprint 204204COMPLETED0
70588Sprint 204204COMPLETED0
70694Sprint 202202INCOMPLETED2
70694Sprint 203203COMPLETED2
70695Sprint 202202INCOMPLETED2
70695Sprint 203203COMPLETED2
70710Sprint 202202COMPLETED0
70720Sprint 202202INCOMPLETED0
70720Sprint 203203COMPLETEDnull
70787Sprint 202202INCOMPLETED2
70787Sprint 203203INCOMPLETED2
70787Sprint 204204COMPLETED2
70789Sprint 203203INCOMPLETED0
70789Sprint 204204INCOMPLETED3
70789Sprint 205205COMPLETED3
70868Sprint 202202COMPLETED0
71109Sprint 202202COMPLETED0
71286Sprint 202202COMPLETED0
71325Sprint 202202INCOMPLETED0
71325Sprint 203203COMPLETEDnull
71358Sprint 204204COMPLETED0
71439Sprint 203203COMPLETED0
71499Sprint 203203INCOMPLETED0
71499Sprint 204204COMPLETEDnull
71532Sprint 203203COMPLETED0
71775Sprint 204204COMPLETED0
71880Sprint 203203INCOMPLETED0
71880Sprint 204204COMPLETEDnull
71940Sprint 203203INCOMPLETED0
71940Sprint 204204COMPLETEDnull
71942Sprint 203203INCOMPLETED0
71942Sprint 204204COMPLETEDnull
72035Sprint 204204REMOVED0
72103Sprint 204204COMPLETED0
72218Sprint 204204COMPLETED0
72480Sprint 204204COMPLETED0
72518Sprint 204204COMPLETED0
72547Sprint 204204COMPLETED0
72556Sprint 204204INCOMPLETED0
72556Sprint 205205COMPLETEDnull
72559Sprint 204204INCOMPLETED0
72559Sprint 205205COMPLETEDnull
72578Sprint 204204INCOMPLETED0
72578Sprint 205205INCOMPLETEDnull
72598Sprint 205205INCOMPLETEDnull
72599Sprint 205205COMPLETEDnull
72600Sprint 205205INCOMPLETEDnull
72766Sprint 205205COMPLETED0
72821Sprint 205205INCOMPLETED0
72963Sprint 205205INCOMPLETED0
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Done.

     

    Result

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZZNTgMxDIWvgmbNInbsOFlDF0iUIkBsqh6ADUKI+4v0dzqpPeN0MepIn57ePDuOt9uBCYmG++H95/fr++8OA9aX4/Nhs359Xn2sHuv/MOzut0PCEMo1HA/w/vn04sDpgJOuzYJJN6Jqt/iClRYfrbyt1pvPazQBRHciCUrWbShwhkTuPDJI7PjAFh+1pzjrOB9wbqyc4cTgKY2B65mMsGFkqh1PuJSiW9Hg+luG6QiXlBzKp7RLRnbbqLB44iMdtwpv4HpTHWEJUEBP+/YjJTSjYa6397AxGjSYKC97PsOcO+BUjHE2DQ91XHc9wkbRLW32awsEb/dVGA1YGw03+HxpJLuaFXXcalYD12s5wh23zQ1uTcCo4/pROMM5OSbJyQhAcE8SAczGtafB0Ro7aiItPlt4iOw/ZkDRf9qBSk8dW3zeCEf0GxFhv3LOxqnRXTf4vHahLu0GX9I2ErG00a1ddYwA2y1KEIKxj2i6CP7eQ+pIGrlHmckxnS4wGxulmnOLz968FXaNMgNf0BYjEUN7ilv72QUvnXiH8xRCj7akjsQzGouRrl2Sa2ut+O4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ISSUE_KEY = _t, SPRINT_NAME = _t, SPRINT_NUMBER = _t, SPRINT_REPORT_STATUS = _t, STORY_POINTS_COMMITTED = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"STORY_POINTS_COMMITTED", Int64.Type}, {"ISSUE_KEY", Int64.Type}, {"SPRINT_NUMBER", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"ISSUE_KEY"}, {
        {"All", each _, type table [SSUE_KEY=nullable number, SPRINT_NAME=nullable text, SPRINT_NUMBER=nullable number, SPRINT_REPORT_STATUS=nullable text, STORY_POINTS_COMMITTED=nullable number]},
        {"Points", each List.Max([STORY_POINTS_COMMITTED]), Int64.Type} }),
    BufferedPoints = List.Buffer(GroupedRows[Points]),
    GenerateHelper = List.Generate(
        ()=> [ x = 0, y = BufferedPoints{x} ],
        each [x] < List.Count(BufferedPoints),
        each [ x = [x]+1, y = [y] + BufferedPoints{x} ],
        each [y]
),
    Merged = Table.FromColumns(Table.ToColumns(GroupedRows) & { {0} & List.RemoveLastN(GenerateHelper, 1)}, Value.Type(GroupedRows & #table(type table[Helper=Int64.Type],{}))),
    Ad_InnerColumn = Table.AddColumn(Merged, "All With Total Column", each Table.AddColumn([All], "Total Story Points Committed", (x)=> x[STORY_POINTS_COMMITTED] + [Helper], Int64.Type), type table),
    Combined = Table.Combine(Ad_InnerColumn[All With Total Column])
in
    Combined

     

5 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi, could you privide expected result for issue 68173 and one or two more please?

  • JeevanMallya's avatar
    JeevanMallya
    Resolver II
    2 years ago

    Yes the requirement here is the sum all the individual story points keeping in mind to consider only Unique issue keys (Issues Key only once) if they appear in multiple sprints.Consider the ones with highest story points for summation.

    So as in case of 68173 we should consider 5 (Highest story point) only once for summation.I would like to replace all null with zero for STORY_POINTS_COMMITTED column and add a new column called Total_Committed_Story_Points and sum it considering the issue key with Max story points once.Something like below

    ISSUE_KEYSPRINT_NAMESPRINT_NUMBERSPRINT_REPORT_STATUSSTORY_POINTS_COMMITTEDTotal Story Points committed
    54244Sprint 202202COMPLETED00
    62009Sprint 203203INCOMPLETED00
    62009Sprint 204204COMPLETED00
    65726Sprint 202202INCOMPLETED00
    65726Sprint 203203INCOMPLETED00
    65726Sprint 204204REMOVED00
    66113Sprint 202202COMPLETED00
    66198Sprint 203203COMPLETED00
    68164Sprint 204204COMPLETED00
    68173Sprint 203203INCOMPLETED00
    68173Sprint 204204INCOMPLETED55
    68173Sprint 205205COMPLETED55
    68651Sprint 202202INCOMPLETED510
    68651Sprint 203203COMPLETED510
    68653Sprint 205205INCOMPLETED313
    68799Sprint 202202COMPLETED316
    68888Sprint 202202COMPLETED420
    69669Sprint 202202COMPLETED020
    69825Sprint 202202COMPLETED323
    69827Sprint 202202INCOMPLETED427
    69827Sprint 203203INCOMPLETED427
    69827Sprint 204204COMPLETED427
    70191Sprint 205205COMPLETED027
    70244Sprint 203203COMPLETED027
    70249Sprint 203203COMPLETED027
    70448Sprint 204204COMPLETED027
    70588Sprint 204204COMPLETED027
    70694Sprint 202202INCOMPLETED229
    70694Sprint 203203COMPLETED229
    70695Sprint 202202INCOMPLETED231
    70695Sprint 203203COMPLETED231
    70710Sprint 202202COMPLETED031
    70720Sprint 202202INCOMPLETED031
    70720Sprint 203203COMPLETED031
    70787Sprint 202202INCOMPLETED233
    70787Sprint 203203INCOMPLETED233
    70787Sprint 204204COMPLETED233
    70789Sprint 203203INCOMPLETED033
    70789Sprint 204204INCOMPLETED336
    70789Sprint 205205COMPLETED336
    70868Sprint 202202COMPLETED036
    71109Sprint 202202COMPLETED036
    71286Sprint 202202COMPLETED036
    71325Sprint 202202INCOMPLETED036
    71325Sprint 203203COMPLETED036
    71358Sprint 204204COMPLETED036
    71439Sprint 203203COMPLETED036
    71499Sprint 203203INCOMPLETED036
    71499Sprint 204204COMPLETED036
    71532Sprint 203203COMPLETED036
    71775Sprint 204204COMPLETED036
    71880Sprint 203203INCOMPLETED036
    71880Sprint 204204COMPLETED036
    71940Sprint 203203INCOMPLETED036
    71940Sprint 204204COMPLETED036
    71942Sprint 203203INCOMPLETED036
    71942Sprint 204204COMPLETED036
    72035Sprint 204204REMOVED036
    72103Sprint 204204COMPLETED036
    72218Sprint 204204COMPLETED036
    72480Sprint 204204COMPLETED036
    72518Sprint 204204COMPLETED036
    72547Sprint 204204COMPLETED036
    72556Sprint 204204INCOMPLETED036
    72556Sprint 205205COMPLETED036
    72559Sprint 204204INCOMPLETED036
    72559Sprint 205205COMPLETED036
    72578Sprint 204204INCOMPLETED036
    72578Sprint 205205INCOMPLETED036
    72598Sprint 205205INCOMPLETED036
    72599Sprint 205205COMPLETED036
    72600Sprint 205205INCOMPLETED036
    72766Sprint 205205COMPLETED036
    72821Sprint 205205INCOMPLETED036
    72963Sprint 205205INCOMPLETED036
    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      Done.

       

      Result

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZZNTgMxDIWvgmbNInbsOFlDF0iUIkBsqh6ADUKI+4v0dzqpPeN0MepIn57ePDuOt9uBCYmG++H95/fr++8OA9aX4/Nhs359Xn2sHuv/MOzut0PCEMo1HA/w/vn04sDpgJOuzYJJN6Jqt/iClRYfrbyt1pvPazQBRHciCUrWbShwhkTuPDJI7PjAFh+1pzjrOB9wbqyc4cTgKY2B65mMsGFkqh1PuJSiW9Hg+luG6QiXlBzKp7RLRnbbqLB44iMdtwpv4HpTHWEJUEBP+/YjJTSjYa6397AxGjSYKC97PsOcO+BUjHE2DQ91XHc9wkbRLW32awsEb/dVGA1YGw03+HxpJLuaFXXcalYD12s5wh23zQ1uTcCo4/pROMM5OSbJyQhAcE8SAczGtafB0Ro7aiItPlt4iOw/ZkDRf9qBSk8dW3zeCEf0GxFhv3LOxqnRXTf4vHahLu0GX9I2ErG00a1ddYwA2y1KEIKxj2i6CP7eQ+pIGrlHmckxnS4wGxulmnOLz968FXaNMgNf0BYjEUN7ilv72QUvnXiH8xRCj7akjsQzGouRrl2Sa2ut+O4f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ISSUE_KEY = _t, SPRINT_NAME = _t, SPRINT_NUMBER = _t, SPRINT_REPORT_STATUS = _t, STORY_POINTS_COMMITTED = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"STORY_POINTS_COMMITTED", Int64.Type}, {"ISSUE_KEY", Int64.Type}, {"SPRINT_NUMBER", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"ISSUE_KEY"}, {
        {"All", each _, type table [SSUE_KEY=nullable number, SPRINT_NAME=nullable text, SPRINT_NUMBER=nullable number, SPRINT_REPORT_STATUS=nullable text, STORY_POINTS_COMMITTED=nullable number]},
        {"Points", each List.Max([STORY_POINTS_COMMITTED]), Int64.Type} }),
    BufferedPoints = List.Buffer(GroupedRows[Points]),
    GenerateHelper = List.Generate(
        ()=> [ x = 0, y = BufferedPoints{x} ],
        each [x] < List.Count(BufferedPoints),
        each [ x = [x]+1, y = [y] + BufferedPoints{x} ],
        each [y]
),
    Merged = Table.FromColumns(Table.ToColumns(GroupedRows) & { {0} & List.RemoveLastN(GenerateHelper, 1)}, Value.Type(GroupedRows & #table(type table[Helper=Int64.Type],{}))),
    Ad_InnerColumn = Table.AddColumn(Merged, "All With Total Column", each Table.AddColumn([All], "Total Story Points Committed", (x)=> x[STORY_POINTS_COMMITTED] + [Helper], Int64.Type), type table),
    Combined = Table.Combine(Ad_InnerColumn[All With Total Column])
in
    Combined