Forum Discussion
Susan513
Helper II
Helper IIRoll up average per 2 categories- custom column
I am looking to get the average SPI per project, per bucket and put that value in the summary row - this is the row with the same task name as the bucket. I am looking to put the data into a Gantt ch...
Hi Susan513, check this:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJPC4IwGMfxtyI7i8z9ye1Y0KGbh27DwwiJkSwpg15+o8dCxT0TLxO+8BsfNYbs+75rSU5O3g3ODu7uw8vZPm9ZGQ600Io0uSF1ax9Ipei3OlgfHqQT0P1G68567/x1Mamnk+uNXAyuV0rP5o7v9vJaXiqbjiHFfwpp4pgsHMqCUhyTwa1VChM6IVFMiHSFYUKzq3BMqKTAMVkSk23AZBsw+cpvEq2SmNCpCsUcI4lhzq4VxeTTbxzF5ElMvgFzbJoP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Buckets = _t, #"Task Name" = _t, SPI = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"SPI", type number}}, "en-US"), GroupedRows = Table.Group(ChangedType, {"Project", "Buckets"}, {{"T", each _{0} & [Task Name = [Buckets]{0}, SPI = List.Average([SPI])] , type table}}), CombinedT = Table.FromRecords(GroupedRows[T], Value.Type(Table.FirstN(ChangedType, 0))) in CombinedT
