Forum Discussion
Calculate average subprojects grouped by parent project
Hi,
My organization has a KPI that follows the number of subprojects that are created at one single time. That is measured by the number of project id:s per parent project. I need to both count the number of project id:s per parent project as well as finding the average number of project id:s.
This table is very simplified. There will other slicers like Region, sales rep, created date etc.
Both parent project anc project id is text.
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])))
3 Replies
- RobbeVL
Impactful Individual
Hi,
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
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])))
