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
Jarrod
Helper III
Helper III

Measure to get Count For Employee, average count for team and average count for all employees

Hi,

 

I have tables as below:

 

Task Table

EmployeeTaskDate
JohnA01/01/2022
JohnA02/01/2022
JohnA3/01/2022
JohnB1/01/2022
JohnB2/01/2022
JackA1/01/2022
JackB2/01/2022
FredA1/01/2022
FredB2/01/2022

 

Employee Table

Employee Team
JohnCountry A
JackCountry A
FredCountry B

 

I am wanting to get the count per task for the employee, average count for the employee team and average count for all employees.

 

I am able to get the count per employee by using count aggregation for task but am struggling to get correct measure for average for team and all employees.

 

Desired output when selecting John as the employee and all date ranges.

 

Task   EmployeeCount   TeamAverage   AllAverage
A321.666666667
B21.51.333333333

 

Any assistance would be appreciated.

1 ACCEPTED SOLUTION

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

14 REPLIES 14
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur,

 

This is great. How would I modify this to take into account an additional employee that has not completed any tasks? 

 

EG I have added Bill and the average includes Bill as 0 lowering the Team & all average. I would like the average to only include employees where Task Count >= 1.

 

Jarrod_0-1654394586309.png

 

Share a dataset with the scenario you have described and very clearly show the expected result there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I'm not able to upload pbix. I have slighly modifed your pbix by adding an additional employee as below.

 

Additional Employee:

Jarrod_0-1654398219765.png

 

 

Current & Expected Result:

Jarrod_2-1654398274672.png

 

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Perfect, thankyou!

You are welcome.  If my previous reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AlB
Super User
Super User

Hi @Jarrod 

Assuming a relationship between the two tables, see the attached file for a possible solution

TeamAvg = 
VAR currentTeam_ = SELECTEDVALUE(EmployeeT[Team]) 
VAR teamT_ = CALCULATETABLE(DISTINCT(EmployeeT[Employee]), EmployeeT[Team] = currentTeam_, ALL(EmployeeT[Employee]))
RETURN
AVERAGEX(teamT_, CALCULATE(COUNT(TaskT[Employee])))

 

AllAvg = 
AVERAGEX(ALL(EmployeeT[Employee]), CALCULATE(COUNT(TaskT[Employee])))

 

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

 

Hi @AlB,

 

Thankyou for your response. The report contains a filter for Employee. Both AllAvg and TeamAvg display the same value as Employee count. 

 

Jarrod_1-1654379110227.png

 

Is it possible to disregard the filter for AllAvg as we are looking at all employees and just take into account the  filter based on team for TeamAvg?

 

Edit: I was able to get the AllAvg working with below:

All Average = 
AVERAGEX(
	ALLEXCEPT(Employees, Employees[Employee]),
	CALCULATE(COUNTA('Task'[Employee]))
)

@Jarrod 
I belive that is what the file I uploaded already does .The resutls are the same as what you showed in your first post

AlB_0-1654411761657.png

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

tamerj1
Super User
Super User

Hi @Jarrod 

I think you mean John not Jack?

Apologies - updated op.

@Jarrod 

I'm having diffulties understanding how did you calculate the team average and the all average 

Hi @tamerj1,

 

John count:

 

AA Employee Count = 3

AA Team Average  = (3[John]+ 1[Jack]) / 2 = 2.

AA All Average = (3 + 1 + 1)/3 = 1.666667

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.