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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lc1
Helper III
Helper III

Help to create percentage formula using sum of various values divided by unique value

Good morning,

I need to calculate % Absenteeism by department. The data I have available is as follow:

Capture.PNG

The formula I'm trying to use is sum of PAR-HOURS (24) divided by 72 (instead of 72*3). And have the formula work for all employees during one pay period selected.

 

Thanks,

LC

20 REPLIES 20
AlB
Super User
Super User

Hi @lc1

 

Is the "Leave Hours" value always the same for a  Period/employee? If so:

DIVIDE(SUM(Table[PAR_HOURS]);

             VALUES(Table[Leave Hours]))

No, it is usually 80 hours biweekly per pay period, but it can change (like the example above)

Also, I get an error when I add the rest of the employees to the calculation.

In the example above it does not change, it's 72 for all of them

That's only one employee of many. Total employees are approximately 4000 and each take time off. 

For example out of 80 hours paid in a period, 16 could've been sick time which means 16/80=20% Absenteeism for that specific employee.

The challenge for me is add each employees PAR-Hours and divide them by each employee Leave Time. And get a total % Absenteeism by Department.

@lc1

Ok, so if you set up a matrix with rows employee ID and period then you have the details of one employee per row and thus

 VALUES(Table[Leave Hours]))

will provide you with a scalar. That assumes this value is the same for the selected employee in the selected period, as the 7 shown in your table

 

Hi @lc1,

 

I made one sample for your reference. If it doesn't meet your requirement, kindly share your pbix and excepted result to me. You can upload the files to onedrive and share the link here.

 

Create the measure as below.

 

Measure = CALCULATE(SUM('Table'[PAR_HOURS]),ALL('Table'),VALUES('Table'[EMPLID]))/SUM('Table'[Leave Hours])

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @lc1,

 

I cannot access the link as you shared. Could you please share the file again by another way?

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

I have an excel file but don't know how to share it other than by mail or screen shot.

@lc1

 

You could upload the file here and share the URL (no sign-in required)

ttp://www.filedropper.com/datasample_1

 

I hope this is it (link to share)

I'm looking for monthly % of absenteeism for Occ Code 8050 for division 71. Based on past reporting methods results should be around 20%. (Using only Unsheduled Absenteeism, already noted in the Report level filters)

% Absenteeism = AVERAGEX(VALUES(Absenteeism[EMPLID]),CALCULATE(SUM(Absenteeism[PAR_HOURS])/MIN(Absenteeism[Leave Hours])))

 

How can I adjust this formula to select the "MIN(Table[Leave Hours])" per pay period? The Leave Hours value will be the same per pay period but could change every month.

When I try to combined all employees from the same division this is what I get ( huge percentage)

 

Capture 1.PNG

How do you define the measure for all employees, as the average of all of them? If so, try this:

 

AVERAGEX (
    VALUES ( Table[EmplID] ),
    CALCULATE ( SUM ( Table[PAR_HOURS] ) / VALUES ( Table[LeaveHours] ) )
)

 

The measure should be:

Sum of PAR_Hrs per employee divided by Leave Hours (note that I only need 1 value of Leave Hours not the sum of them)

 

  

 Employee 1Employee 2Employee 3Total Department
 PAR_HoursLeave HoursPAR_HoursLeave HoursPAR_HoursLeave HoursPAR_HoursLeave Hours
 356880880  
 2456880    
 156880    
 856      
 856      
 456      
Total4856248088080216
%Abs(48/56)86%(24/80)30%(8/80)10%(80/21637%

 

 

It's not adding them, VALUES() gives you just one value

I'm using your formula and I'm getting this error message:

Error.PNG

It's the VALUES(...) then

Assuming the value Table[Leave Hours] is the same for a period/employee you're showing, you can use MIN, or MAX, or AVERAGE  It's hard fro me to do any more without the  data model

 

 

 

AVERAGEX (
    VALUES ( Table[EmplID] ),
    CALCULATE ( SUM ( Table[PAR_HOURS] ) / MIN ( Table[Leave Hours] ) )
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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