I have a Tableau Desktop 10.5 workbook of data showing projects my department manages and the time spent on each project by various departments. I need to provide the percentage of the project's total that each department spent on each project.

In the data, I have a dimension showing the total time spent on the project (Actual Hours) and each department has a total number of hours spent on the project (Total Hours).

I have a calculated field (% of Work Req Total Hours) with this formula summing the total hours for each department for each work req and dividing it by the actual hours for that effort.

For most of the departments, this works as you can see below.

-- Contract M... spent 101 hours out of 2772.5 resulting in 3.6% of this project's total.

However, in this example, CS PMO has a total of 2469 hours spent on the project across two manager codes. The sum is correct, but the formula I am using now computes the total hours for each manager code and divides it by the total hours and then averages those two percentages (12.65% and 76.39%) to get 44.52% for CS PMO when it should be** 89%**.

I'm not sure what I'm missing, and I'm relatively new to Tableau and table calculations especially, so any help or direction of what I should be researching is much appreciated.

EDIT: The workbook has been updated so that more than one effort is displayed so you can see how the percentage of total calculation doesn't work.