Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have tables as below:
Task Table
Employee | Task | Date |
John | A | 01/01/2022 |
John | A | 02/01/2022 |
John | A | 3/01/2022 |
John | B | 1/01/2022 |
John | B | 2/01/2022 |
Jack | A | 1/01/2022 |
Jack | B | 2/01/2022 |
Fred | A | 1/01/2022 |
Fred | B | 2/01/2022 |
Employee Table
Employee | Team |
John | Country A |
Jack | Country A |
Fred | Country 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 |
A | 3 | 2 | 1.666666667 |
B | 2 | 1.5 | 1.333333333 |
Any assistance would be appreciated.
Solved! Go to Solution.
You may download my PBI file from here.
Hope this helps.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
Share a dataset with the scenario you have described and very clearly show the expected result there.
I'm not able to upload pbix. I have slighly modifed your pbix by adding an additional employee as below.
Additional Employee:
Current & Expected Result:
You may download my PBI file from here.
Hope this helps.
Perfect, thankyou!
You are welcome. If my previous reply helped, please mark it as Answer.
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])))
|
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.
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
|
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. |
Apologies - updated op.
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |