Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
@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
)