Forum Discussion
Help with taking a sum and allocating it for shift staffing
Hello all!
I am STUMPED with this one and would be incredibly grateful for any help. This is for a manpower study due for my company in the next couple of weeks. They want it in Power BI so it can remain dynamic and adjust as time goes on if needed. I have everything worked out except this.
Through a series of DAX calculations, I have determined on any given shift (using 1st shift for this example) the minimum staffing needs. The next (and last) step for me is calculating the amount of individuals that can take a day off. Each individual, who is assigned a "slot" will get three days off. They are SSM (Sat, Sun, Mon), SMT (Sun, Mon, Tue), etc.
In this example, I have 11 people on this shift and I need DAX to allocate those individuals in a sort of round robin manner until there are no more left to distribute, starting with the "slot" that has the highest value, then to the next, then to the next, repeating until all "11" have been "spent".
Each of the "slots" below is a separate measure, as is "Personnel". The "# Off" is the number returned by each measure. The "Indv Off Work" is the desired Result.
| Personnel: | 11 | |
| Slots | # Off | Indv Off Work |
| SSM | 1.30 | 1 |
| SMT | 1.52 | 2 |
| MTW | 1.88 | 2 |
| TWT | 1.89 | 2 |
| WTF | 1.73 | 2 |
| TFS | 1.44 | 1 |
| FSS | 1.24 | 1 |
Hopefully that all makes sense. Thank you in advance for any and all help!
dvonigas Hi!
First, rank the slots in a calculated column:
Slot Rank =
RANKX(
ALL('Slots'),
'Slots'[# Off],
,
DESC,
DENSE
)Second, create a calculated column:
Cumulative Personnel Assigned =
VAR TotalPersonnel = [Personnel] -- This is your total number of people, e.g., 11
VAR SlotsCount = COUNTROWS(ALL('Slots'))
VAR MaxRounds = INT(DIVIDE(TotalPersonnel, SlotsCount))
VAR Remainder = MOD(TotalPersonnel, SlotsCount)
VAR ThisRank = [Slot Rank]
VAR NumFullRounds = MaxRounds
VAR AdditionalIfNeeded = IF(ThisRank <= Remainder, 1, 0)
RETURN
NumFullRounds + AdditionalIfNeededYou'll obtain your desidered output:
BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
7 Replies
- BeaBF
Super User
dvonigas Hi!
First, rank the slots in a calculated column:
Slot Rank =
RANKX(
ALL('Slots'),
'Slots'[# Off],
,
DESC,
DENSE
)Second, create a calculated column:
Cumulative Personnel Assigned =
VAR TotalPersonnel = [Personnel] -- This is your total number of people, e.g., 11
VAR SlotsCount = COUNTROWS(ALL('Slots'))
VAR MaxRounds = INT(DIVIDE(TotalPersonnel, SlotsCount))
VAR Remainder = MOD(TotalPersonnel, SlotsCount)
VAR ThisRank = [Slot Rank]
VAR NumFullRounds = MaxRounds
VAR AdditionalIfNeeded = IF(ThisRank <= Remainder, 1, 0)
RETURN
NumFullRounds + AdditionalIfNeededYou'll obtain your desidered output:
BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
- BeaBF
dvonigas Ah, you can do the second step also as a measure:
Indv Off Work =VAR TotalPersonnel = 11VAR SlotsCount = COUNTROWS(ALL('Slots'))VAR MaxRounds = INT(DIVIDE(TotalPersonnel, SlotsCount))VAR Remainder = MOD(TotalPersonnel, SlotsCount)VAR ThisRank = MAX('Slots'[Slot Rank])VAR NumFullRounds = MaxRoundsVAR AdditionalIfNeeded = IF(ThisRank <= Remainder, 1, 0)RETURNNumFullRounds + AdditionalIfNeededBBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
Thank you! I will give this a try and let you know how it goes! I greatly appreciate it!
