cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
annade22 Frequent Visitor
Frequent Visitor

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.

 

Average # subproject.png

1 ACCEPTED SOLUTION

Accepted Solutions
annade22 Frequent Visitor
Frequent Visitor

Re: 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]))
)
3 REPLIES 3
RobbeVL Established Member
Established Member

Re: Calculate average subprojects grouped by parent project

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

annade22 Frequent Visitor
Frequent Visitor

Re: Calculate average subprojects grouped by parent project

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.

 

ParentProjectProjectIDNo subprojectsAverage number of subprojects
 30000002  
3000000230000002-00111,75
 30000003  
3000000330000003-00111,75
 30000011  
3000001130000011-001  
 30000021  
3000002130000021-00121,75
3000002130000021-00221,75
 30000041  
 30000051  
3000005130000051-00131,75
3000005130000051-00231,75
3000005130000051-00331,75
annade22 Frequent Visitor
Frequent Visitor

Re: 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]))
)

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 281 members 2,902 guests
Please welcome our newest community members: