Sum FTE based on Distinct Employee IDs in Another Column

[ahiemstra]

I’m looking for some help with a measure. I have a table where column A lists Employee ID, column B lists the Department, and column C lists the Full-Time Equivalent (FTE). Each employee is listed in the table multiple times given the nature of the dataset. A sample of the data looks like this:

I need to calculate the total FTE represented in the dataset but not overstate the FTE given that an employee's FTE will repeat multiple times in the dataset. Here's where it gets convoluted.

If the employee's department is the same, but the FTE changes, I almost need to “average” the FTE so the overall FTE represented is accurate (see Employee ID 35 as an example.)

If the employee’s FTE is distributed across different departments, I need to “sum” the FTE so the overall FTE is accurate (see Employee ID 75 as an example.)

Looking at the sample dataset shown above, this should result in a total of 4.10 FTE as shown below:

I’m not sure if what I am asking is possible. Thoughts?

[ThxAlot]

[olgad]

Hi, try this one: 

Measure = CALCULATE(SUMX(SUMMARIZE('Table', 'Table'[Employee id], 'Table'[Department]), AVERAGE('Table'[FTE])))