1 of 1 people found this helpful
You can try this table calculation for marks - RANK_PERCENTILE(SUM([Marks])/TOTAL(SUM([Marks])),'asc')
and for attendance - RANK_PERCENTILE(SUM([Attendance])/TOTAL(SUM([Attendance])),'asc')
this will give the percentile based on the total marks and attendence of the of the students
then create a calculated field using the same and drop it to color shelf to see top 80% students.
it wil be like this -
IF RANK_PERCENTILE(SUM([Attendance])/TOTAL(SUM([Attendance])),'asc') >= 0.8 THEN 1
by using this u can create two buckets first for students above 80% and another with less than 80%
i ve created this sample data set for ur reference.
Thank you Akash and Madhura, appreciate the help but the level of detail should be at a higher level not lowest level (student)
Sorry I should explained better. Although your solutions will work when I drop the name on columns or row shelf, what I am looking for is just two bars at overall level. 'X' number of students fell into red bucket and 'Y' number of members fell into green bucket. I am not looking to break it at an individual level because this is just an example dataset and my actual data contains more than hundred thousand entries and I want to look at highest level and then break it into subjects. But just two bars. Hope this makes it clear.
Also , I want to look at running total percentage because percentile is not working. Something like this
if RUNNING_SUM(SUM([Attendance])) / TOTAL(SUM([Attendance]))
<= 0.80 THEN 'Green' else 'Red' END
I might need to use LOD and fix it at Student level but I am unable to combine table and LOD expressions.