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. He...
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

     

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