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:
| Quarter | Activity | TimeSpent [h] |
| Q1 | Activity 1 | 3 |
| Q2 | Activity 1 | 2 |
| Q3 | Activity 3 | 6 |
| Q3 | Activity 4 | 1 |
| Year Quarter | Number of employees |
| Q1 | 5 |
| Q2 | 2 |
| Q3 | 8 |
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.
7 Replies
Hi Anonymous
Seems pretty easy enough. Do you have sample expected output results?
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 Hi 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)
Hi,
PBI file attached.
Hope this helps.
Ashish_Mathur thank you, this really helped me. It turns out I had to change relations between the tables