Forum Discussion
Non classic average calculation
Dear colleagues
I have one basic question with possible answer already
As due to complexiti of the old model, i cant swith model to calculate averega activiy of the employee, so i found one workarrond
Example for June 2024 (Acitivity is pretty long calculation of different contacts with customer, depends also on the channel and type of event)
| Employee name | Activity | DaysOff | UserActiveDays |
| Employee 1 | 7.06 | 1.000 | 18.000 |
| Employee 2 | 7.00 | 3.000 | 16.000 |
| Employee 3 | 6.24 | 0.250 | 18.750 |
Question i have: if i sum Activity and devide by number of Employees i get average 6.77, which i think its not ok, but if i remove Employees from the rows, i get Average activitity 6.75. My basic knowledge explains me, 2nd one is more correct as rows are not equal/does not have same weight: Employee 3 did in more days less activity and therefore id decrease average for all?
Thank you for your feedback!
Hello al1981 ,
To calculate a more accurate "average activity per active day" across employees, you should use a weighted average formula:
Weighted Average = Activity*Active Days /Active Days
This will account for differences in the number of days employees were active and therefore provide a more balanced view of the average activity.
In your example:
- Employee 1: 7.06×18.00=127.087.06 \times 18.00 = 127.087.06×18.00=127.08
- Employee 2: 7.00×16.00=112.007.00 \times 16.00 = 112.007.00×16.00=112.00
- Employee 3: 6.24×18.75=117.006.24 \times 18.75 = 117.006.24×18.75=117.00
Sum of weighted activities: 127.08+112.00+117.00=356.08
Sum of active days: 18.00+16.00+18.75=52.75
So, the weighted average activity would be= 356.08/52.75 = 6.75
This is closer to the second value you mentioned, and it's more accurate because it takes into account the different active days for each employee.
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!
Thank You
Dharmendar S
4 Replies
Hello al1981 ,
To calculate a more accurate "average activity per active day" across employees, you should use a weighted average formula:
Weighted Average = Activity*Active Days /Active Days
This will account for differences in the number of days employees were active and therefore provide a more balanced view of the average activity.
In your example:
- Employee 1: 7.06×18.00=127.087.06 \times 18.00 = 127.087.06×18.00=127.08
- Employee 2: 7.00×16.00=112.007.00 \times 16.00 = 112.007.00×16.00=112.00
- Employee 3: 6.24×18.75=117.006.24 \times 18.75 = 117.006.24×18.75=117.00
Sum of weighted activities: 127.08+112.00+117.00=356.08
Sum of active days: 18.00+16.00+18.75=52.75
So, the weighted average activity would be= 356.08/52.75 = 6.75
This is closer to the second value you mentioned, and it's more accurate because it takes into account the different active days for each employee.
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!
Thank You
Dharmendar S
Hi al1981 ,
dharmendars007 lbendlin , thanks for your concern about this case. I tried to create sample data to perform weighted calculations.Please check if there is anything that can be improved. Here is my solution:
1\My data source (Table)
2\Create a measure
Result = SUMX('Table','Table'[Activity]*'Table'[UserActiveDays])/SUM('Table'[UserActiveDays])Best Regards,
Bof
Dear dharmendars007 , dear Anonymous , thank you to both! If i understand: both of yours are "same" or at least are taking same solution. And i do agree: weighted average is something i need in my model! Many tnx to both of you!
You need to decide what is more meaningful in your scenario - the average of averages, the average over all values, or a weighted average.
