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
JeevanMallya
Frequent Visitor

Unique Sprint Story Points Calculation

‎04-19-2024 04:35 AM

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
Labels:
Message 1 of 6
222 Views
0
1 ACCEPTED SOLUTION
dufoq3
Super User
Super User
In response to JeevanMallya

‎04-20-2024 06:00 AM

Done.

 

Result

dufoq3_0-1713617983242.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

Message 4 of 6
163 Views
0
5 REPLIES 5
JeevanMallya
Frequent Visitor

‎04-21-2024 09:23 PM

Thank you

Message 5 of 6
125 Views
0
dufoq3
Super User
Super User
In response to JeevanMallya

‎04-21-2024 09:32 PM

You're welcome.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 6 of 6
115 Views
0
JeevanMallya
Frequent Visitor

‎04-19-2024 07:20 PM

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
Message 3 of 6
173 Views
0
dufoq3
Super User
Super User
In response to JeevanMallya

‎04-20-2024 06:00 AM

Done.

 

Result

dufoq3_0-1713617983242.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 4 of 6
164 Views
0
dufoq3
Super User
Super User

‎04-19-2024 09:32 AM

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Message 2 of 6
189 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
Top Solution Authors
View All
Top Kudoed Authors
View All