Forum Discussion
Calculate average with reference to another table - matrix
Hi Anonymous ,
My apologies, seems like you've already added up number of employees in your other table. In that case, you'll just need to do the sum:
Employees = SUM(employees[Number of employees])then divide the two measures:
Average Employee Time = VAR _Hours = [SUM_Activities12] VAR _Employees = [Employees] // Employees = SUM(employees[Number of employees]) RETURN DIVIDE(_Hours, _Employees)Based on your sample data I would return this expected output:
Hi,
PBI file attached.
Hope this helps.
Hi hnguy71 , result would be matrix which looks something like this: (numbers are random)
| Quarter | Total time spent per quarter | Average time spent per employee per quarter |
| Q1 | 34 | 4 |
| Q2 | 22 | 5 |
| Q3 | 68 | 7 |
Super UserHi Anonymous ,
Got it.
I am going to assume that you have both tables connected to a date table and the Quarter is coming from there. All you would need to do is create a new measure to return the average time spent per employee:
Average Employee Time =
VAR _Hours = [SUM_Activities12]
VAR _Employees = DISTINCTCOUNT(YOUR_EMP_TABLE[YOUR_EMP_COLUMN])
RETURN
DIVIDE(_Hours, _Employees)
Hello,
I created the measure but it seems like it divides by the count of rows rather than corresponding number of employees in each Quarter. My emp table looks like this:
Quarter Number of employees Q1 1 Q2 2 Q3 3 - hnguy71
Hi Anonymous ,
My apologies, seems like you've already added up number of employees in your other table. In that case, you'll just need to do the sum:
Employees = SUM(employees[Number of employees])then divide the two measures:
Average Employee Time = VAR _Hours = [SUM_Activities12] VAR _Employees = [Employees] // Employees = SUM(employees[Number of employees]) RETURN DIVIDE(_Hours, _Employees)Based on your sample data I would return this expected output: