Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Sum of duplicated values but with the same id(key)

Hello,

 

I have an issue solving a problem.

So, there is the following table called 'Issues'

 

 

Key        Sprint       Story Points

12951    Sprint1         2

12951    Sprint1         2

12951    Sprint2         2

12951    Sprint2         2

12951    Sprint2         2

12952    Sprint1         3

12952    Sprint1         3

 

The output that I would like is to have a extra column so that would look like this:

 

Key        Sprint       Story Points    Story Points Custom Column

12951    Sprint1          2                             0

12951    Sprint1          2                             2

12951    Sprint2          2                             0

12951    Sprint2          2                             0

12951    Sprint2          2                             2

12952    Sprint1         3                             0

12952    Sprint1         3                             3

 

I need this because let say I have BI page where I put a slicer filter on Sprint and I select from there 'Sprint 1'.

And for 'Sprint 1'(selected) I should have a measure/column to show in a visual total of Story Points for 12951 & 12951  for that sprint and that being: 2 + 3 = 5. not 2+2+3+3

 

Can someone help me with this?

 

 

Thank you a lot! 🙂 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @NewbiePowerBye,

 

To what I can understand you want to sum the distinct storypoints for each of the Sprints, in this case use the following measure:

 

 

Measure =
SUMX (
    FILTER (
        SUMMARIZE ( Issues; Issues[Key]; Issues[Sprint]; Issues[Story Points] );
        SUM ( Issues[Story Points] )
    );
    Issues[Story Points]
)

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5

Hello,

 

Is there any way to do the same, but from Power Query Editor?

 

Thanks in advance!

Hi,

 

Try this M code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSyNDVU0lEKLijKzCsBsYyUYnWIFTeiSNwIxXxj3OKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Key = _t, Sprint = _t, #"Story Points" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Sprint", type text}, {"Story Points", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Key]&[Sprint]),
    Partition = Table.Group(#"Added Custom", {"Custom"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Key", "Sprint", "Story Points", "Index"}, {"Key", "Sprint", "Story Points", "Index"}),
    #"Grouped Rows" = Table.Group(#"Expanded Partition", {"Custom"}, {{"Count", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Index1", each Table.RowCount([Count])),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom1", "Count", {"Key", "Sprint", "Story Points", "Index"}, {"Key", "Sprint", "Story Points", "Index"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded Count", "Custom.1", each if [Index]=[Index1] then [Story Points] else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Index", "Index1"})
in
    #"Removed Columns"

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@NewbiePowerBye

 

Using Power Query..try

See file attached as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSyNDVU0lEKLijKzCsBsYyUYnWIFTeiSNwIxXxj3OKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Key = _t, Sprint = _t, #"Story Points" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", Int64.Type}, {"Sprint", type text}, {"Story Points", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Key", "Sprint"}, {{"All", each Table.AddIndexColumn(_,"Index",0, 1),type table}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Story Points", "Index"}, {"Story Points", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each if [Index]=0 then [Story Points] else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Key", Order.Ascending}, {"Sprint", Order.Ascending}, {"Custom", Order.Ascending}})
in
    #"Sorted Rows"

Regards
Zubair

Please try my custom visuals
MFelix
Super User
Super User

Hi @NewbiePowerBye,

 

To what I can understand you want to sum the distinct storypoints for each of the Sprints, in this case use the following measure:

 

 

Measure =
SUMX (
    FILTER (
        SUMMARIZE ( Issues; Issues[Key]; Issues[Sprint]; Issues[Story Points] );
        SUM ( Issues[Story Points] )
    );
    Issues[Story Points]
)

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you @MFelix

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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