Calculating Over/Under of Staff to Events

[Thornell]

Hi all,

I'm trying to create a measure to get an Over/Under of Staff needed for events.

I've got a count of events over the course of each hour for the day which are separated into 4 types:

Super, High, Standard and MTX.

I then have a count of the total staff on shift during these hours, and each staff is only allowed to do certain events.

I'm then wanting to take the total number of staff, and minus the events for the day to get an Over/Under, but I need to have it so the number will minus that person from the total count as the staff could be across the multiple types.

For example:

I have 20 staff over the course of that day.

All 20 can do MTX,

12 can do Standard and below

7 can do High and below

and 3 can do all types.

I need the measure to take this into account, so when there's an event that's a Super type, it minuses from the staff, but only if there is a staff who can do that type available, and so on for all types, but it needs to minus the Super events first, then High, then Standard and then whatever's left is for MTX.

The below is an example of what I'd expect using some fake data:

So after the one Super is assigned, the 2 leftover people can go into High, meaning the 7 becomes 6.

Once High is then assigned, I'd need -3 and need 3 more High Staff, and that'd leave 4 Standard and 13 MTX left.

And again for Standard down to 1 leftover and 10 leftover MTX, then the rest after MTX assigned is our Over/Under

meaning I'd be:

Super +/- 0

High -3

Standard +1

and MTX +6

I can't share the file data as it contains sensistive information, and the screenshot above is all I can give.

Can anybody help?