DAX to calculate Number of days presence in a week for Attendance

[Sunilkulkarni]

Hi 

This is for the Attendance module, for a school, 

I have a Table, Attendance, which has Dates and students Present or Absent, if present it is "1" and Absent is "0". in the coloumn, school presence .

I want to represent in the Stacked bar chart as below.  below Num are no. of students present 

Ex : In Week 1 for 1 day in a week how many Present, 2days How many, 3 days, 4days.....

What is The DAx to calculate this. and how to represent in Stacked Bar chart.

Week	1Day	2 Days	3 Days	4Days	5Days
1	10	35	30	45	10
2
3
4

[Daniel29195 · Accepted solution]

@Sunilkulkarni 

sample data i used . 

output 

steps : 

step 1 : 

create the following table :  in power query . ( use enter data ) 

step 2 :  create this measure

users attendance = 
Var ds = 
ADDCOLUMNS(
    VALUES(ATTENDANCE[STUDENT ID]),
    "@x" ,  CALCULATE(SUM(ATTENDANCE[school presence .]))
    )

var ds1 = 
GROUPBY(
    ds,
    [@x],
    "count" , countx(CURRENTGROUP(), 1)
)

var  dayss = MAX(days[days])

RETURN 
SELECTCOLUMNS(
FILTER(
    ds1,
    value([@x]) = value(dayss)
),
[count]
)

let me know if this works for you 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Daniel29195 · Accepted solution]

@Sunilkulkarni

if you want totals at the row level 

use this modified version of the code : 

users attendance = 
Var ds = 
ADDCOLUMNS(
    VALUES(ATTENDANCE[STUDENT ID]),
    "@x" ,  CALCULATE(SUM(ATTENDANCE[school presence .]))
    )

var ds1 = 
GROUPBY(
    ds,
    [@x],
    "count" , countx(CURRENTGROUP(), 1)
)

var  dayss = values(days[days])

return


SUMX(
FILTER(
    ds1,
    value([@x]) in (dayss)
),
[count]
)

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Sunilkulkarni]

Its Very Helpful, Thanks a ton.

I have another situation to calculate the % of Presence , I need to calculate % of Students presence only for students who have attended school 4 days and above for a week, This count of students will be divided by total no. of students. Kindly help with DAX

[Daniel29195]

hello @Sunilkulkarni 

output 

 base on the sample data above, we have 1 student that did attend >=4days   .

thus 1/10 = 10% 

if this logic is correct kindly use this measure : 

users attendece % = 
Var ds = 
ADDCOLUMNS(
    VALUES(tbl[studentid]),
    "@x" ,  CALCULATE(SUM(tbl[schoole presence]))
    )

var ds1 = 
GROUPBY(
    ds,
    [@x],
    "count" , countx(CURRENTGROUP(), 1)
)

var  dayss = MAX(days[days])

var c = 
SELECTCOLUMNS(
FILTER(
    ds1,
    value([@x]) = 4
),
[count]
)

return
c / CALCULATE(DISTINCTCOUNT(tbl[studentid]), all(tbl))

let me know if this works for you . 

NB: if it is possible, i would suggest to create the table in the backend in the case that this structure of table is needed for further calculations .  and this way your dax code would be much simpler . 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Sunilkulkarni]

Hi @Daniel29195 

Thanks for the DAX, But it is not working, im getting 1.72% which i feel not correcrt out of 5000 Students.

If i want to calculate for >2 days presence how would i do that, i tried in above Dax its not working. For Ex in above table ,  it has to 5 Students whoc are more than 2 days presence in a week

[Daniel29195]

@Sunilkulkarni 

did you change from 4 to 2  in the code in the section at the end?  

[Sunilkulkarni]

Yes I did, but if i cange to 2 , it will only calculate Presence 2, i want to count students who is present >, 2,3 or 4 ,5days.

[Daniel29195]

@Sunilkulkarni 
ok i got you 

Change the static number to  

selectedvalue( days[days] ) 

Now this should work base on the days in the filter context 

now if you only want to calculate when you have days >=2 

then  you can simply at the return add a condition that if selectedvalue( days[days] ) <2 , blank(), c/ calculate(distinctcount(tbl[studentid) , all(tbl))

[Sunilkulkarni]

No Daniel, Its not working, please find below code i used. I want calculate %

Total No. of Students Present for more than 3days a week/toal No. of students.

In Total No. of Students Present for more than 3days a week it should add all the students who have come more than 3 days a week for a month

Users attendece % =

Var ds =

ADDCOLUMNS(

    VALUES(Attendance[StuID]),

    "@x" ,  CALCULATE(SUM(Attendance[School_Presence_Count]))

    )

var ds1 =

GROUPBY(

    ds,

    [@x],

    "count" , countx(CURRENTGROUP(), 1)

)

var  dayss = MAX(WeekDays[Day])

var c =

SELECTCOLUMNS(

FILTER(

    ds1,

    value([@x])=selectedvalue( WeekDays[Day])  ),[count]

)

return

if (selectedvalue( WeekDays[Day]) <2 , blank(), c / CALCULATE(DISTINCTCOUNT(Attendance[StuID]), all(Attendance))

)

[Daniel29195]

@Sunilkulkarni 

@Sunilkulkarni 

output base on sample data : 

sample data : 

users attendece % = 
Var ds = 
ADDCOLUMNS(
    VALUES('Table'[student id]),
    "@x" ,  CALCULATE(SUM('Table'[presence]))
    )

var ds1 = 
GROUPBY(
    ds,
    [@x],
    "count" , countx(CURRENTGROUP(), 1)
)


RETURN 

SUMX(
FILTER(
    ds1,
    value([@x]) in VALUES(dimdays[days]) &&  [@x]>=2
),
[count]
)
/  CALCULATE(DISTINCTCOUNT('Table'[student id]), all('Table'))

If my answer helped sort things out for you, i would appreaciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Sunilkulkarni]

Hi , Im getting below out put 

In the card it is showing very less %, Weekwise total % is correct, but when it is cumulative in single card, % is wrong, how to Fix.

[Daniel29195 · Accepted solution]

@Sunilkulkarni 

sample data i used . 

output 

steps : 

step 1 : 

create the following table :  in power query . ( use enter data ) 

step 2 :  create this measure

users attendance = 
Var ds = 
ADDCOLUMNS(
    VALUES(ATTENDANCE[STUDENT ID]),
    "@x" ,  CALCULATE(SUM(ATTENDANCE[school presence .]))
    )

var ds1 = 
GROUPBY(
    ds,
    [@x],
    "count" , countx(CURRENTGROUP(), 1)
)

var  dayss = MAX(days[days])

RETURN 
SELECTCOLUMNS(
FILTER(
    ds1,
    value([@x]) = value(dayss)
),
[count]
)

let me know if this works for you 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Daniel29195 · Accepted solution]

@Sunilkulkarni

if you want totals at the row level 

use this modified version of the code : 

users attendance = 
Var ds = 
ADDCOLUMNS(
    VALUES(ATTENDANCE[STUDENT ID]),
    "@x" ,  CALCULATE(SUM(ATTENDANCE[school presence .]))
    )

var ds1 = 
GROUPBY(
    ds,
    [@x],
    "count" , countx(CURRENTGROUP(), 1)
)

var  dayss = values(days[days])

return


SUMX(
FILTER(
    ds1,
    value([@x]) in (dayss)
),
[count]
)

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[Sunilkulkarni]

Thanks Daniel for the solution, I was struggling a lot to solve this issue, Thanks a ton for the help. if possible can you please explain how this measure works.

[Daniel29195 · Accepted solution]

@Sunilkulkarni  sure.

step1 : 

var ds 

will get the sum of days where the student were present per week per student.

now that that you have w temp table having the students, and each student how many times was present .

you need a way to count the nb of students per the nb of days present .

step2:

so this is where groupby functions comes in handy .( u can use summarize. it also works  )

using groupby, you groupby the calculated column created in step 1 ,  which is the nb of days present, 

and you get the count of students .

last step : 

since you have the nb of days in the columns : 1 ,2 , 3, 4, 5 

you return the sum of the count of students calculated in the previous step (step2) , 

and you filter base on the nb of days existing in the filter context ( example. in column 1 in the matrix, you have nbofdays  = 1 , thus you need to get the sum of count of students where days ( [@x] = 1 ) ,  

and so on .

hope this makes sense . 

If my answer helped sort things out for you, i would appreciate a thumbs up👍and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠