Forum Discussion
Helper IAttendance or count based on 2 fields
Hi,
I have a table, as below and I need to count the employee attendance for the whole month. I want the answer as ABC =3, PQR=4, CDF=3. If I use the count of (Login date) in a matrix it gives correct, but the total does not come as correct. Also I want it based on Division for each month, or week. How do I use this. I used the sumx function, but it is not working.
| Division | Employee Name | Login Date | Time | Day of Week | Location |
| 1 | ABC | 24/01/2023 00:00 | 50:17.0 | Tuesday | Atrium Door RHS |
| 1 | ABC | 25/01/2023 00:00 | 33:52.0 | Wednesday | Atrium Door RHS |
| 1 | ABC | 31/01/2023 00:00 | 54:14.0 | Tuesday | Atrium Door RHS |
| 1 | PQR | 11/01/2023 00:00 | 07:44.0 | Wednesday | Atrium Door RHS |
| 1 | PQR | 31/01/2023 00:00 | 25:03.0 | Tuesday | 2nd Floor Lift Lobby Door LHS |
| 1 | PQR | 11/01/2023 00:00 | 24:06.0 | Wednesday | Atrium Door RHS |
| 2 | PQR | 12/01/2023 00:00 | 12:56.0 | Thursday | Atrium Door RHS |
| 2 | CDF | 13/01/2023 00:00 | 12:43.0 | Friday | Atrium Door RHS |
| 2 | CDF | 16/01/2023 00:00 | 12:08.0 | Monday | Atrium Door RHS |
| 2 | CDF | 17/01/2023 00:00 | 13:19.0 | Tuesday | Atrium Door RHS |
9 Replies
- Selva-Salimi
Solution Sage
Hi rains_23
first add a column to your table as follows:
year_month= format( date , 'YYYYMM')
and then write a measure as follows:
measure employee_count := var tbl = summarize (table , division, employee , year_month ,"cnt", count (employee))
return
sumx(tbl , cnt )
If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
Hi rains_23
When using the count of “Login Date” in the matrix, the total is 10, isn't that what you want? May I ask what you expect the total to be?
If I use the count of (Login date) in a matrix it gives correct, but the total does not come as correct.Here is my test result:
Create new columns with the following DAX:
MonthYear = FORMAT([Login Date], "YYYY-MM")WeekNumber = WEEKNUM([Login Date])Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rains_23The total does not come correct for me ,
Hi rains_23
There is too little information to see from this screenshot, could you please create a matrix visual showing us the problem you are experiencing using the sample data from the initial post?
And describe all the relevant fields in the matrix and the measures that might be used.
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- rains_23
Sorry, I was going to post the data I used. All are simple columns. Month is a colum duplicated from Login date. with month format. What it is doing is it is taking the total as 21 in above example, that is number of employees in the month of jan. and not no. of occurence. So If an employee is in month of jan 3 times, it is counting as 1 and not 3
