Hi,
I've spent the last few days to figure out a way to calculate the average of a measure of all individuals in a team, but in vain. I hope that you could give me a hint.
I have a Matrix visual where leaders can choose a team in a slicer, then the Matrix will dynamically show the employees in the columns (# of columns varies depending on the # of employees in a team). The last column is obviously the total of all employees in a team. The challenge here is to add another column for the average. So, columns would be: Metric, Employee 1, employee 2..... employee n, team average, total. How could I accomplish that ?
It might be impossible. In that case, I think about a simpler way: creating a new measure, and add it as a row. So, the rows would be different metrics, such sales, average team sales, revenu, average team revenue, expenses, average expenses, etc. Since the table has different sales items for each employee, I can't simply write : average (Sales), because that would return the average sales of each employee. For example, employee 1 has an average sale per item of $10K, employee 2 has an average $12K and so on. That's not what I want. I'd like like to have the average of the whole team; so the same average would repeat for each employee in the same team. For example, there are only 2 employees: one with sales of $10K, the other has $12K. Then the team average is $11K; and that $11K will repeat for employee 1 and 2.
How could I write that new measure that calculates the average of the whole team ? And be reminded that # of employees (# of columns) varies, depending on the team.
Thank you very much for your help.
