Inter-office Strava "light" system: Devide sum by external factor

[Biccbacc]

Hi!

I need help with a stacked colum chart. 

I am running a contest at work where you register all your activites to and from work. cycling, running, walking etc. 

My data comes from a power BI dataset, constantly updated from a sharepoint list. The sharepoint list consists of thousands of elements. Each element has data on: Name, office branch, department, activity, distance 

Visualizing this in power bi is a lot of fun, but there is one chart I cant get to work:

I want to show sum of distance, devided by employees in branch. 

As it is now, I can only show sum of distance PER branch. Some branches has hundreds of emplyees, while others only have a few. The current chart is pretty useless, as its really just a chart of office-size. 

Not all employes in every branch is participating, but I have the correct numbers of employees in each branch, its just not included in the dataset. 

How can i devide the sum from each branch by the number of employees in each branch, and show that average in a chart?

[TeigeGao · Accepted solution]

Hi @Biccbacc ,

According to your description, my understanding is that you want to calculate the divide of total distance with the count of employee for each branch.

In this scenario, we can create a relationship between the employee table and activity table, then create a measure like below:

Measure = DIVIDE(SUM(activite[activite]),COUNT(employee[employee]))

The result will like below:

Best Regards,

Teige