Calculate % of shifts filled by department by day

[scott3387]

Hello,

Sorry this is probably really easy but I'm trying to produce a table for management so they can see departments that are understaffed in advance. I have two tables and two lookups (Examples below).

• Filled - Rows contain one assignement aka one person working in one unit for say 8 hours.
• Unfilled - Rows contain shifts without someone assigned (generally due to sickness or annual leave)
• LUs are just heirarchies

I thought the calculation 

(sum('Filled Duties'[Actual Work])/(sum('Filled Duties'[Actual Work])+sum('Unfilled Duties'[Work Time])))

would work but sadly not. Also being a measure it removes the ability to drill down into the data which I would like to keep.

What is the best way to accomplish this?

Thanks in advance

Filled
Assignment Info	Owning Unit	Person	Duty Date	Actual Work	Shift	Cost Centre	Fulfilment Type	Shift Type	Assignment No	Grade
Joe Bloggs - E on 18/04/2020 in Ward 21 (TW)	Ward 21 (TW)	Joe Bloggs	18 April 2020	7.5	Early (E)	Ward 21 (TW)	Agency	Day		Band 5 RN
UnFilled
Valid Date	Owning Unit	Shift Type	Planned Start Date	Planned End Date	Start Time	End Time	Work Time	Rest Time	Grade	Grade Type	Grade Type Category
11-May-20	Clinical Admin Unit - Head and Neck	Day	11/05/2020 09:00	11/05/2020 17:00			7.5	0.5	A&C	A&C	-
LU Unit
Healthroster Unit	Cost Centre	Org L2	Org L3	Site
Chief Executive	AA102	Test	Corporate and Support	Test
LU Grade
Grade	Staff Group	Secondary Grade
A&C	Administrative and Clerical	A&C

[v-eachen-msft]

Hi @scott3387 ,

Do the "Filled" and "Unfilled" tables have a relationship?

According to the sample data, the calculated result is just a percentage value.

You could show the expected result here.