Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
annade22
New Member

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
annade22
New Member

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

View solution in original post

3 REPLIES 3
annade22
New Member

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]))
)
RobbeVL
Impactful Individual
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.

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.