cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

Re: Calculate % of shifts filled by department by day

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
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors