Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Calculate % of shifts filled by department by day



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
Assignment InfoOwning UnitPersonDuty DateActual WorkShiftCost CentreFulfilment TypeShift TypeAssignment NoGrade 
Joe Bloggs - E on 18/04/2020 in Ward 21 (TW)Ward 21 (TW)Joe Bloggs18 April 20207.5Early (E)Ward 21 (TW)AgencyDay Band 5 RN 
Valid DateOwning UnitShift TypePlanned Start DatePlanned End DateStart TimeEnd TimeWork TimeRest TimeGradeGrade TypeGrade Type Category
11-May-20Clinical Admin Unit - Head and Neck Day11/05/2020 09:0011/05/2020 17:00  7.50.5A&CA&C-
LU Unit           
Healthroster UnitCost CentreOrg L2Org L3Site       
Chief Executive AA102TestCorporate and SupportTest       
LU Grade           
GradeStaff GroupSecondary Grade         
A&CAdministrative and ClericalA&C         


Community Support
Community Support

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.


Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.