Forum Discussion
Calculate average with reference to another table - matrix
I'm struggling with the below case. I have created a measure which calculates total number of hours per certain activities: SUM_Activities12 = CALCULATE(SUM(activities[TimeSpent]),activities[Activi...
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.
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
Super User
Super UserHi 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: