Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to Solution.
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! 🤠
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 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! 🤠
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
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! 🤠
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
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.
@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))
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! 🤠
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.
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! 🤠
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! 🤠
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.
@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! 🤠
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
189 | |
96 | |
67 | |
63 | |
53 |