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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
scott3387
Frequent Visitor

Calculate % of shifts filled by department by day

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 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 
            
UnFilled           
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         

 

1 REPLY 1
v-eachen-msft
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

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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