Forum Discussion
Solved
Generate TOTAL_INSPRINTBUGS_SPRINTWISE and show Issue Key that can be used for Drill through later
I have the following table PROJECT_KEY SPRINT_NAME ISSUE_KEY YEAR ABC ABC Sprint 01 ABC-390 2022 ABC ABC Sprint 01 ABC-392 2022 ABC ABC Sprint 02 ABC-436 2022 ABC ABC Spr...
This can be done in Power Query much easier. Here's the M Code.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc47CoAwEATQq4TUCsluvqV6BMuQA9iIiPdHJRG7ZJplBh7MpiSneZHDe8V6nNt+CaVLHzmqJ5EiknloQ2pDqtCwQ6FHYWhD/qBjEPrOtK8wWBh2fgwVRtYFchcSCg0KLQo9CDWjEJwO8Yf5Bg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROJECT_KEY = _t, SPRINT_NAME = _t, ISSUE_KEY = _t, YEAR = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROJECT_KEY", type text}, {"SPRINT_NAME", type text}, {"ISSUE_KEY", type text}, {"YEAR", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"PROJECT_KEY", "SPRINT_NAME", "YEAR"}, { {"ISSUE_KEY", each Text.Combine([ISSUE_KEY], ","), type text}, {"TOTAL_INSPRINTBUGS_SPRINTWISE", each Table.RowCount(_), Int64.Type} }) in #"Grouped Rows"Results:
Thank you very much
Thank you very much
Create a Calculated Table
SummarizedTable =
SUMMARIZE(
'Table',
'Table'[PROJECT_KEY],
'Table'[SPRINT_NAME],
'Table'[YEAR],
"ISSUE_KEYS", CONCATENATEX('Table', 'Table'[ISSUE_KEY], ","),
"TOTAL_INSPRINTBUGS_SPRINTWISE", COUNT('Table'[ISSUE_KEY])
)
Create measure:
Rolling_Average =
CALCULATE(
AVERAGE(SummarizedTable[TOTAL_INSPRINTBUGS_SPRINTWISE]),
DATESINPERIOD(SummarizedTable[SPRINT_NAME], MAX(SummarizedTable[SPRINT_NAME]), -3, MONTH)
)
💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn