cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BrotherZin
Frequent Visitor

Need help with creating a measure

I have two tables:

Absence Detail which lists each planned absence an employee has requested by day (table includes date, employee, department among a few other columns.

 

Department Total which is a list of departments and the total employees in each.

 

There is a one to many relationship between DT and AD through the department.

 

I'm trying to develop a measure that will calculate the percentage of employees scheduled absent over the total number of employees in that particular department.

 

I have a Table Tile which has the Date and Department and a measure which gives me the total employees planned off for the corresponding date/department  in the table, but have had a bit of difficulty determining how to properly incorporate the department total from the DT table so I can calculate a percentage.

 

I got part of the Measure:  calculate(COUNTX(AD,[EmployeeNumber]))

 

but need to find the "divided by department total" for the % of employes planned to be out.

 

Once I have that my next step is to filter that calculation so that the only records that will show are those department where more than X% of their employee base is scheduled off so that the supervisor can ensure there is coverage.

 

I would appreciate any assistance.  I hope I have provided enough detail.  Thank you.

 

 

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@BrotherZin,

 

You may use measure below and take advantage of Visual level filters.

Measure =
DIVIDE ( COUNTX ( AD, [EmployeeNumber] ), MAX ( DT[total employees] ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@BrotherZin,

 

You may use measure below and take advantage of Visual level filters.

Measure =
DIVIDE ( COUNTX ( AD, [EmployeeNumber] ), MAX ( DT[total employees] ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you!  Now that I see it, it makes perfect sense!

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors