cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Average of the team ?

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.

3 REPLIES 3
Super Contributor

## Re: Average of the team ?

Hi @zzzzoooo ,

How are you dynamically changing the number of columns in the visual?

Thanks,

Nathaniel

Frequent Visitor

## Re: Average of the team ?

By default, the rows in a Matrix visual are dynamic, i.e. automatically expand or shrink, depending on # input. Well, there's a control to swap from dynamic rows to columns; it's called "Show on rows", under the "Values" format.

With that control, my columns are dynamic, and my rows are static. I have a fixed # of rows, each represents a metric. And, I'd like to add a new row representing the team average, and I don't know how. The formula "Average(Sale)" would give me the average sale per item for each employee, and not the team average as I wish.

Community Support Team

## Re: Average of the team ?

hi, @zzzzoooo

You may use ALLSELECTED Function in your Average measure to get it.

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

If you still have the problem, please share some simple sample data and your expected output.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)