Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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! 🙂
Solved! Go to Solution.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello,
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.
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"
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
124 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |