Forum Discussion
Calculate average subprojects grouped by parent project
After long search, I found a solution that works. If anyone wants to use it here it is:
Count of Projects_ProjectID average per Projects_ParentProject =AVERAGEX(KEEPFILTERS(VALUES('NumberProjects'[Projects_ParentProject]));CALCULATE(COUNTA('NumberProjects'[Projects_ProjectID])))
Impactful IndividualHi,
It seems like you should clean your data properly first.
The easiest will be to do this in PowerQuery.
My main remark here, why are there blank cells?
I would just split up the project ID by Delimiter "-"
Then you would replace the blank value with whatever you like.
To count the number of parents & projects, you can just use distinct count.
I have no Idea if my reply will have been usefull, as I dont fully understand the dataset.
If you want more exact help, please always add a current state and a required state.
Robbe
Thanks for your quick reply.
First to clarify, the project id is one single column. It can be both a standalone project, a subproject or a parent project. A subproject is defined by referring to its parent project. Hence, the blank values. One option can very well be to filter the parent projects only.
What I would like to achieve is something like this where parent project 30000002 has 1 subproject (defined by the suffix -001) wheareas project 30000051 has 3 subprojects. I cannot rely on the numbering of the suffix. I must make a proper count, since projects can be deleted.
| ParentProject | ProjectID | No subprojects | Average number of subprojects |
| 30000002 | |||
| 30000002 | 30000002-001 | 1 | 1,75 |
| 30000003 | |||
| 30000003 | 30000003-001 | 1 | 1,75 |
| 30000011 | |||
| 30000011 | 30000011-001 | ||
| 30000021 | |||
| 30000021 | 30000021-001 | 2 | 1,75 |
| 30000021 | 30000021-002 | 2 | 1,75 |
| 30000041 | |||
| 30000051 | |||
| 30000051 | 30000051-001 | 3 | 1,75 |
| 30000051 | 30000051-002 | 3 | 1,75 |
| 30000051 | 30000051-003 | 3 | 1,75 |