Did some testing and digging on my end and best I can figure out is that you would need to either:
A - do calculations in your SQL query
B - create some custom code for it
As a challenge to myself, I went with option B as I've never done custom code inside SSRS before. Turns out it is pretty easy (if you know VB.NET). But for this particular case, it is quite easy.
1- Go to the report properties
2- Select the Code tab
3- enter the following code into the Code tab:
public dim totalFTE = 0
public dim WorkHours = 40
public function CalculateFTE (TotalHours As Integer, Weeks As Integer) As Double
dim result As Double
result = TotalHours/Weeks/WorkHours
If (result > 1)
result = 1
totalFTE = totalFTE + result
Feel free to rename any variables or functions as you see fit, but make sure that you remember that you changed them as they will be used later. Also, WorkHours could be made private, I just had it public as I wanted to see it in the report as I was calculating things out.
4- change your FTE calculation to:
5 - the last step is that total calculation. The code for that bad boy is:
The last step is calculating the average. totalFTE contains the SUM of the FTE values calculated above and we want to get the average, so we just need to count the number of distinct RC values. Alternately, you could modify the code to include a "call count" which would be declared at the top below the totalFTE value, as public and an integer with a starting value of 0. Then each time the function is called, you increment it by 1 and then at the end, instead of doing that COUNTDISTINCT in step 5, you divide by the counter you variable you created. If you have a very large data set, the counter variable will likely be faster at the cost of a TINY bit of RAM.
Does that help?
EDIT - just wanted to confirm that this is required to be SSRS 2005? I did my testing in SSRS 2016. If it needs to be 2005, I am not confident that the above will work.
Also, if SQL SERVER is new enough, you could use LEAD to calculate your FTE value on the SQL side. My thought here is you do a CASE with a LEAD to check if the next row ordered by the week number ascending has the same RC value. If true, then 0, else (SUM(hours) OVER (PARTITION BY RC))/(COUNT(hours) OVER (PARTITION BY RC))/40. Then on the report side, you can do a SUM of the FTE to get the FTE per RC and do a SUM of the FTE divided by a COUNTDISTINCT on the RC column.
Problem comes in when you have an older version of SQL Server and it doesn't have LEAD. In that case my first thought on how to handle it is to create a CTE to calculate out the max week number (essentially, WITH data AS (SELECT *, MAX(week#) AS MAXWEEK OVER (PARTITION BY RC) FROM <table>)) and then in the final select do a CASE WHEN week# = MAXWEEK THEN (SUM(hours) OVER (PARTITION BY RC))/(COUNT(hours) OVER (PARTITION BY RC))/40 ELSE 0 END AS FTE. Then same steps on the report side - you can do a SUM of the FTE to get the FTE per RC and do a SUM of the FTE divided by a COUNTDISTINCT on the RC column.