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.
Super UserHi 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 - hnguy71
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)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