Help with taking a sum and allocating it for shift staffing

[dvonigas]

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! 

[BeaBF · Accepted solution]

@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 + AdditionalIfNeeded

You'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 = 11

VAR SlotsCount = COUNTROWS(ALL('Slots'))

VAR MaxRounds = INT(DIVIDE(TotalPersonnel, SlotsCount))

VAR Remainder = MOD(TotalPersonnel, SlotsCount)

VAR ThisRank = MAX('Slots'[Slot Rank])

VAR NumFullRounds = MaxRounds

VAR AdditionalIfNeeded = IF(ThisRank <= Remainder, 1, 0)

RETURN

NumFullRounds + AdditionalIfNeeded

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[dvonigas]

Thank you again for all of this! It works great as a static table, however I am running into an issue as all of the "slots" are measures. I created a disconnected table in an attempt to use the RANKX function however now I am dealing with a calculation issue with the new table. Any thoughts on this? Sorry, I am only about 7 months into using Power BI and am still figuring some of these parts out. 

Here is the table I created, with the code:

Days off = DATATABLE("Days Off",STRING, {{"SSM"}, {"SMT"},{"MTW"}, {"TWT"}, {"WTF"}, {"TFS"}, {"FSS"}})

Num Off =

IF('Days off'[Days Off] =  "SSM", CALCULATE(CFS[SSM], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),

    IF('Days off'[Days Off] = "SMT", CALCULATE(CFS[SMT], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),

        IF('Days off'[Days Off] = "MTW", CALCULATE(CFS[MTW], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),

                IF('Days off'[Days Off] = "WTF", CALCULATE(CFS[WTF], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),

                    IF('Days off'[Days Off] = "TFS", CALCULATE(CFS[TFS], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),  CALCULATE(CFS[FSS], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1"))

)))))

 

Here is what I originally sent and each measure in a visual gives this result. Both tables have the exact CONTAINSEXACTSTRING filter. The original I sent (pasted below) is the desired result.

Slots	# Off
SSM	1.30
SMT	1.52
MTW	1.88
TWT	1.89
WTF	1.73
TFS	1.44
FSS	1.24

There are no additional filters being applied to either dataset (that I am aware of, other than the calculations within the measures). Any insight would be VERY MUCH appreciated. 

Also, I did try using the SWITCH function for the calculated column but kept getting a text data type error so I went with nested IF statements. 

[BeaBF]

@dvonigas Try to:

1. Create a separeted table like:

Days off = DATATABLE("Days Off",STRING, {{"SSM"}, {"SMT"},{"MTW"}, {"TWT"}, {"WTF"}, {"TFS"}, {"FSS"}})

2. Change your Num Off measure with:

Num Off =
VAR Slot = SELECTEDVALUE('Days off'[Days Off])
RETURN
SWITCH(TRUE(),
Slot = "SSM", CALCULATE(CFS[SSM], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),
Slot = "SMT", CALCULATE(CFS[SMT], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),
Slot = "MTW", CALCULATE(CFS[MTW], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),
Slot = "TWT", CALCULATE(CFS[TWT], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),
Slot = "WTF", CALCULATE(CFS[WTF], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),
Slot = "TFS", CALCULATE(CFS[TFS], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),
Slot = "FSS", CALCULATE(CFS[FSS], CONTAINSSTRINGEXACT(CFS[Locations], "Location 1"))
)

Now rank them:

Slot Rank =
RANKX(
ALL('Days off'),
[Num Off],
,
DESC,
DENSE
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[dvonigas]

This worked great, thank you!  Although my values in the separated table are still different. I'm gonna keep plugging away to figure out why. The calculations are accurate when its just the measures themselves, but once they are in the table there is clearly something being filtered/not being filtered I have to figure out. 

But your "round robin" measure was fantastic. Thank you again!

[BeaBF]

@dvonigas You can try to put an ALL Function to remove the unwanted context:

Num Off =
VAR Slot = SELECTEDVALUE('Days off'[Days Off])
RETURN
SWITCH(TRUE(),
Slot = "SSM", CALCULATE(CFS[SSM], ALL('Days off'), CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),
Slot = "SMT", CALCULATE(CFS[SMT], ALL('Days off'), CONTAINSSTRINGEXACT(CFS[Locations], "Location 1")),
...
)

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[dvonigas]

Thank you! I will give this a try and let you know how it goes! I greatly appreciate it!