DAX / Power Query to calculate % of employees working from home, in the field, etc.

[yosemite]

Hello everyone,

I'm developing a dashboard to calculate the percentage and/or number of staff:

• working from home (TELE)
• in the office (OFFICE)
• doing field work (Field)
• on medical leave (ML)

Note that employees can be on a 5-day or 4-day work week. RDO = Regular Day Off. Here's a sample dataset.

Week	Employee Name	Sun	Mon	Tue	Wed	Thur	Fri	Sat
04/12/2020 - 04/18/2020	Employee1	RDO	ML	ML	ML	ML	ML	TELE
04/12/2020 - 04/18/2020	Employee2	RDO	Field	Field	Field	Field	RDO	RDO
04/19/2020 - 04/25/2020	Employee3	RDO	Office	Office	Office	Office	Office	RDO
04/12/2020 - 04/18/2020	Employee4	RDO	TELE	TELE	TELE	TELE	TELE	RDO
04/05/2020 - 04/11/2020	Employee4	RDO	TELE	TELE	TELE	TELE	RDO	RDO
04/12/2020 - 04/18/2020	Employee5	RDO	TELE	TELE	TELE	TELE	TELE	RDO
04/12/2020 - 04/18/2020	Employee6	RDO	TELE	TELE	TELE	TELE	RDO	RDO
04/12/2020 - 04/18/2020	Employee7	RDO	TELE	TELE	TELE	TELE	TELE	RDO
04/12/2020 - 04/18/2020	Employee8	RDO	RDO	TELE	TELE	TELE	Office	RDO
04/12/2020 - 04/18/2020	Employee9	RDO	Field	TELE	TELE	TELE	TELE	RDO
04/12/2020 - 04/18/2020	Employee10	RDO	Office	Office	Office	Office	RDO	RDO
04/19/2020 - 04/25/2020	Employee11	RDO	RDO	TELE	TELE	TELE	TELE	RDO
04/12/2020 - 04/18/2020	Employee12	RDO	TELE	TELE	TELE	TELE	RDO	RDO
04/12/2020 - 04/18/2020	Employee13	RDO	TELE	TELE	TELE	TELE	RDO	RDO
04/12/2020 - 04/18/2020	Employee14	RDO	Field	Field	Field	Field	RDO	RDO
04/19/2020 - 04/25/2020	Employee15	RDO	TELE	TELE	TELE	TELE	RDO	RDO
04/12/2020 - 04/18/2020	Employee16	RDO	RDO	TELE	TELE	TELE	TELE	RDO
04/19/2020 - 04/25/2020	Employee17	RDO	RDO	TELE	TELE	TELE	TELE	RDO
04/12/2020 - 04/18/2020	Employee18	RDO	TELE	TELE	TELE	TELE	RDO	RDO
04/12/2020 - 04/18/2020	Employee19	RDO	TELE	TELE	TELE	TELE	RDO	RDO
04/12/2020 - 04/18/2020	Employee20	RDO	RDO	TELE	TELE	TELE	TELE	RDO

 Thank you. 

[AlB]

Hi @yosemite 

how exactly would you calculate it when you have an employee that has a mix, for instance TELE and Office?

Cheers

[yosemite]

Hi @AlB - Thanks for replying.

Is there a way to calculate the employees that has a mix?  75% home, 25% in the office or 50% home, 50% in the office?

If this is not possible, can I create a rule to exclude these employees and still capture the percentage or number of employees using the rest of the dataset?

[dax · Accepted solution]

Hi @yosemite , 

I am not clear about your requirement, you could refer to my sample for details. If this is not what you want,   could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yosemite]

Hello @dax - I hope you'll still remember this post. I now have to filter out two values in the Measure. I have to filter out "RDO" and "Weekend".  How do I update the measure below to filter out two values?

Measure =

CALCULATE (

COUNT( 'Table'[Value] ),

FILTER( 'Table','Table'[Value]<>"RDO" ) ) / CALCULATE(COUNT('Table'[Value]), FILTER(ALLEXCEPT('Table'[EmployeeName],'Table'[EmployeeNo],'Table'[Weekof]),'Table'[Value]<>"RDO"))

Thank you.

[yosemite]

Hey @dax - Your solution worked! THANK YOU SO MUCH! 👏

[yosemite]

Hello @dax - Let me see if this would work; I'll get back to you.

Thanks so much!