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.
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.