Roll up average per 2 categories- custom column

[Susan513]

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 chart - so prefer to utilize this in a custom column if at all possible.

I need to isolate the project, then group the buckets, return the average per bucket, and put this value in the project row where the bucket name is the task name. I am trying to break it down, but just learning and could use some help. I am not sure why Microsoft doesn't include this ability in Project Web any longer.

Sample data:

This is the result section I am trying to calculate. I have manually done the SPI average calculations in excel:

Project	Buckets	Task Name	SPI	Code needed:
Apple	Initiation	Initiation	0.99	Average of Apple Initiation task SPI
Pear	Initiation	Initiation	0.89	Average of Pear Initiation task SPI
Banana	Initiation	Initiation	0.57	Average of Banana Initiation task SPI
Apple	Planning	Planning	0.94	Average of Apple Planning task SPI
Pear	Planning	Planning	0.72	Average of Pear Planning task SPI
Banana	Planning	Planning	0.77	Average of Banana Planning task SPI
Apple	Execution	Execution		Average of Apple Execution task SPI
Pear	Execution	Execution		Average of Pear Execution task SPI
Banana	Execution	Execution		Average of Banana Execution task SPI

This is the additional data (same query)  - where the averages are coming from:

Project	Buckets	Task Name	SPI
Apple	Initiation	Task 1	0.98
Pear	Initiation	Task 1	0.80
Banana	Initiation	Task 1	0.40
Apple	Planning	Task 1	0.99
Pear	Planning	Task 1	0.50
Banana	Planning	Task 1	0.89
Apple	Execution	Task 1
Pear	Execution	Task 1
Banana	Execution	Task 1
Apple	Initiation	Task 2	1.00
Pear	Initiation	Task 2	0.88
Banana	Initiation	Task 2	0.45
Apple	Planning	Task 2	0.97
Pear	Planning	Task 2	0.67
Banana	Planning	Task 2	0.54
Apple	Execution	Task 2
Pear	Execution	Task 2
Banana	Execution	Task 2
Apple	Initiation	Task 3	0.99
Pear	Initiation	Task 3	0.98
Banana	Initiation	Task 3	0.87
Apple	Planning	Task 3	0.85
Pear	Planning	Task 3	0.99
Banana	Planning	Task 3	0.88
Apple	Execution	Task 3
Pear	Execution	Task 3
Banana	Execution	Task 3

All this data is in one query in Power BI named PW Tasks w/ SPI

Appreciate any insight. It seems I need to group by project, then group by bucket, then calculate the average. I am just not certain best way to do this or if possible in a custom column and then have the visual be the Gantt chart. I feel like a measure would be easier - but not sure I can use the data in a Gantt if a measure is used.

[dufoq3 · Accepted solution]

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

[Susan513]

Thank you for putting this together - it doesn't seem to work in a custom column and I am not able to utilize the link to 'learn how to use'

[dufoq3]

[Susan513]

Temporary solution: I made a copy of the query, grouped by project and bucket. Then I can show the average for the SPI field. I am not a fan of duplicating a query essentially, but I believe this is going to give me the correct data for now.