Forum Discussion
Jarrod
Helper III
Helper IIIMeasure to get Count For Employee, average count for team and average count for all employees
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/0...
AlB
Community Champion
Community ChampionHi 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. |
JarrodHelper III
Helper IIIHi 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]))
)- AlB
Jarrod
I belive that is what the file I uploaded already does .The resutls are the same as what you showed in your first postPlease 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.