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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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