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