Calculation with the missing the attendance data

[ckyin999]

Hello everyone,

I need to create a report that shows how many staff members meet the monthly target of entering the office ( swipe card record) at least 8 times per month. Additionally, I need to calculate the percentage of total staff by department that has met this target.

I have created a simple metric to calculate this. However, I noticed that there is a staff member who has never entered the office In June, which means there is no access record for them in Table 2. What should I do to include these records in the table to ensure I get accurate results?

Attend Count = COUNTROWS('Table 2')

Did not meet the 8 times monthly target= COUNTROWS(FILTER(VALUES('Table 2'[badge No]),[Attend Count] <=7))

Example : badge NO = 62396 , '1' indicates that the staff did not meet the 8-times monthly target. Currently, the result shows '0' for June and '1' for July, which is incorrect because the staff didn’t enter the office at all in June.

Following are my table details.

• Table 1: Employee details.

badge No.	Name	Organizational unit 1	Organizational unit 2
62195	AJ	TP	IT
62375	BJ	TP	IT
63291	CJ	TP	Marketing
63317	CJ	TP	Marketing
62472	EJ	TP	Sales
62562	GJ	TP	Admin
65098	HJ	TP	Sales
62396	IJ	TP	IT
62552	JJ	TP	IT
62132	KJ	TP	Facility
62473	LJ	TP	Facility
63320	MJ	TP	Logistic
63235	NJ	TP	Logistic

• Table 2: Daily Employee Access ( June & July record only)

badge No.	Date
62132	4-Jun-24
62473	6-Jun-24
62132	7-Jun-24
62473	7-Jun-24
62132	10-Jun-24
62132	11-Jun-24
62552	13-Jun-24
62473	13-Jun-24
62552	14-Jun-24
62552	20-Jun-24
62473	20-Jun-24
62132	24-Jun-24
62132	25-Jun-24
62132	26-Jun-24
62473	26-Jun-24
62552	27-Jun-24
62132	27-Jun-24
62473	27-Jun-24
62552	28-Jun-24
62132	1-Jul-24
62552	5-Jul-24
62132	9-Jul-24
62473	10-Jul-24
62552	11-Jul-24
62132	15-Jul-24
62132	16-Jul-24
62396	17-Jul-24
62552	18-Jul-24
62473	18-Jul-24
62552	19-Jul-24
62473	19-Jul-24
62132	22-Jul-24
62132	23-Jul-24
62473	24-Jul-24
62552	25-Jul-24
62132	29-Jul-24
62473	29-Jul-24
62132	30-Jul-24
62473	31-Jul-24

My expected result is the following

Month	June	July
Department	Total Staffs	Not Met target	Met %	Total Staffs	Not Met target	Met %
IT	4	4	0%	4	4	0%
Marketing	2	2	0%	2	2	0%
Sales	2	2	0%	2	2	0%
Admin	1	1	0%	1	1	0%
Facility	2	1	50%	2	1	50%
Logistic	2	1	50%	2	1	50%

I would appreciate any help you can provide. Thank you.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[ckyin999]

Hi Ashish,

Many thanks! Yes, it helps. I got the correct answer from your Power BI file and learned how the measures work.

[Ashish_Mathur · Accepted solution]

You are welcome.  Please mark my previous reply as Answer.

[Bmejia]

Create following measures.  Create a Date table (Calendar table and link to table2 on date).  Also for Logistic Department I see them show as 50% in final output.  In the sample provided they never swiped card only IT and Facilities.

Total Staff = CALCULATE(COUNTROWS('Table'))

Not Meet Target =

VAR BadgeCount = CALCULATE(COUNTROWS(Table2))

VAR Met = IF(BadgeCount>7,1,0)

RETURN

If(MET=0,[Total Staff],[Total Staff]-MET)

MET % = DIVIDE([Not Meet Target]-[Total Staff],[Total Staff],0)

[ckyin999]

Hi Bmejia,

Thanks for your response.

It worked based on the sample data provided. However, when I pulled in more data, I noticed different results.

For example: The Change Management department has 9 staff members, but only 2 met the target in June & July. The measurement result, however, is showing 8 instead of 7 in both months.  I can't figure out what is the problem.