4 Replies Latest reply on Aug 9, 2016 1:52 PM by Jeffrey Canham

# Average Income with shifting number of employees

Hey everyone,

I'm fairly new to the software and am having some difficulty figuring out how to get the information that I'm trying to hunt down. Hopefully I'm in the right place to ask this question!

Ideally I'd like to come up with a sheet that tells me:

a) The commission that various sales people brought in each year

b) The average amount of commission only certain employees brought in each year

This would be pretty simple if it was just the average of all employees and the number of employees stayed static, but we've had people come and go. I'm interested in a specific 14 (new employees), but each year has a different number of sales people due to the shifting number of employees. Theoretically it would be: Sum(Commission)/Number of Records of the specified employees, but only if they have a record in that year.

Any ideas on the best way to approach this?

• ###### 1. Re: Average Income with shifting number of employees

You could calculate your denominator (the number of active employees this year) by doing something like;

SUM([Commission])

/

COUNTD(IF datepart('year',[SaleDate]) = datepart('year',Now()) then [EmployeeID] end)

This would give you a distinct count of the employee id's for employee who have a sale in the current year.

1 of 1 people found this helpful
• ###### 2. Re: Average Income with shifting number of employees

Hey Tom,

Thanks for the response, I think I'm getting there! I don't 100% understand the COUNTD and datepart commands (I'll be looking into those asap), but when I follow that calculated field and put it in Rows, I only seem to get a single datapoint for the multiple years I'm looking at. Any idea how I might be able to do it for each year that I want to look at?

Thanks again!

• ###### 3. Re: Average Income with shifting number of employees

The datepart portion of this limits the calculation to look at data for the current year only.

Since you already have a Year dimension in the rows shelf, that is unnecessary and you can simplify to SUM([Commission])/COUNTD([EmployeeID]).

1 of 1 people found this helpful
• ###### 4. Re: Average Income with shifting number of employees

That's perfect, thanks Tom!