Forum Discussion
Method to Sum Totals Per Team Up To Maximum
Friends,
I have provided a simple sample dataset which essentially mirrors my data sample (just much smaller) in the first 3 columns with the 4th column an example of what I'm trying to automate. The teams can produce a total of 2 job lengths per day and they may touch multiple jobs per day totaling more than 2. I would like to show how much of each job each team will complete up to the full 2, and not over 2. Essentially, I want to sum the total job length for each team until I get to 2 or greater, then the last job should be the difference. If there is only one assigned job and it is greater than or equal to 2, it would just be "2". The column "Shift Job Length" is a sample of my desired outcome. Thoughts? Thank you!
| Team | Team Assignment | Total Job Length | Shift Job Length |
| Team 1.1 | 1.1 | 2.1 | 2 |
| Team 2.1 | 2.1 | 0.2 | 0.2 |
| Team 2.2 | 2.2 | 0.4 | 0.4 |
| Team 2.3 | 2.3 | 1.9 | 1.4 |
| Team 3.1 | 3.1 | 1.8 | 1.8 |
| Team 3.2 | 3.2 | 2.0 | 0.2 |
| Team 4.1 | 4.1 | 0.1 | 0.1 |
| Team 4.2 | 4.2 | 0.2 | 0.2 |
| Team 4.3 | 4.3 | 0.3 | 0.3 |
| Team 4.4 | 4.4 | 0.2 | 0.2 |
| Team 4.5 | 4.5 | 0.2 | 0.2 |
| Team 4.6 | 4.6 | 1.4 | 1 |
1 Reply
- amitchandak
Super User
DukeAHolics , Try a new column
New Column =
VAR _TA = 'Table'[Team Assignment]
VAR _JL = 'Table'[Total Job Length]
VAR _CL =
calculate(
sum('Table'[Total Job Length]),
filter(
'Table',
'Table'[Team Assignment] = _TA
&& 'Table'[Total Job Length] <= EARLIER(_JL)
)
)
return
IF(
_CL >= 2,
_JL - (_CL - 2),
_JL
)
