Calculate average by group and display in levels

[avagarwal]

Hi,

I have data in the following format

StudentId	Subject	Marks
1	English	67%
1	Maths	38%
2	English	32%
2	Maths	41%
3	English	74%
3	Maths	87%

I need to display the number of students passed (>= 40%), failed (<40%) in a bar chart. I would like to add a slicer so that the user can select a subject or all subjects. If 'all subjects' is selected, the pass / fail count should be based on the average marks, if a specific subject is selected then based on that subject.

What is the measure that needs to be created for the same? I can add a column indicating pass / failed, but am stuck when all subjects is selected.

Thanks

[Anonymous · Accepted solution]

@avagarwal,

Please create the following measures in your table.

Average mark = AVERAGE(Table1[Marks])
Checkifpassed = IF([Average mark]>=0.4,1,0)
Allstudents = DISTINCTCOUNT(Table1[StudentId])
Passed = IF(COUNTROWS(VALUES(Table1[StudentId]))=1,[Checkifpassed],SUMX(VALUES(Table1[StudentId]),[Checkifpassed]))
Failed = [Allstudents]-[Passed]



Regards,