Forum Discussion
Roll 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 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.
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
4 Replies
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.
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
