Forum Discussion
Ranking averages into quartiles
Hello
Can't find quite what I am after in other posts. The scenario is I have about 50 people each with a result in a variety of subjects, at different points in time. I would like to rank the people into quartiles based on their average over the subjects. The data looks something like this
Currently in excel where the owner averages and then ranks, then manually assigns quartiles. This data will soon be in a SQL server and I have been asked to automate this process. It will be a card filtered by the individual so I can assign a Term filter to the visual, but struggling with the quartiling by average of results.
Hope I am being clear enough and many thanks for any advice in advance.
Pharms
Please find attached the file with the expected outcome:Quartile = VAR __Avg = [Student Average] VAR __Q1 = PERCENTILEX.EXC( ALLSELECTED( 'Table'[Pupil]) , [Student Average] , 0.25 ) VAR __Q2 = PERCENTILEX.EXC( ALLSELECTED( 'Table'[Pupil]) , [Student Average] , 0.50 ) VAR __Q3 = PERCENTILEX.EXC( ALLSELECTED( 'Table'[Pupil]) , [Student Average] , 0.75 ) RETURN SWITCH( TRUE(), __Avg > 0 && __Avg <= __Q1 , "Q1", __Avg > 0 && __Avg <= __Q2, "Q2", __Avg > 0 && __Avg <= __Q3 , "Q3", "Q4" )
3 Replies
Pupil Subject Term Result Pupil Average Quartile A MT22 English 80 A 76.3 Q4 Q1 42.3 B MT22 English 85 B 72.0 Q3 Q2 60.75 C MT22 English 71 C 76.0 Q4 Q3 74 D MT22 English 33 D 43.0 Q2 E MT22 English 35 E 57.9 Q2 F MT22 English 67 F 63.6 Q3 G MT22 English 50 G 41.6 Q1 H MT22 English 84 H 64.4 Q3 A MT22 Maths 66.23 B MT22 Maths 59.07 C MT22 Maths 78.76 D MT22 Maths 63 E MT22 Maths 57 F MT22 Maths 57 G MT22 Maths 41 H MT22 Maths 61 A MT22 French 72 B MT22 French 76 C MT22 French 66 D MT22 French 62 E MT22 French 48 F MT22 French 76 G MT22 French 60 H MT22 French 54 A MT22 Chemistry 62 B MT22 Chemistry 69 C MT22 Chemistry 74 D MT22 Chemistry 26 E MT22 Chemistry 38 F MT22 Chemistry 59 G MT22 Chemistry 33 H MT22 Chemistry 64 A MT22 Geography 85 B MT22 Geography 75 C MT22 Geography 90 D MT22 Geography 48 E MT22 Geography 61 F MT22 Geography 68 G MT22 Geography 40 H MT22 Geography 83 A MT22 History 79 B MT22 History 68 C MT22 History 82 D MT22 History 32 E MT22 History 79 F MT22 History 66 G MT22 History 21 H MT22 History 63 A MT22 Physics 71 B MT22 Physics 71 C MT22 Physics 86 D MT22 Physics 31 E MT22 Physics 62 F MT22 Physics 50 G MT22 Physics 41 H MT22 Physics 60 A MT22 Religious Studies 86 B MT22 Religious Studies 77 C MT22 Religious Studies 80 D MT22 Religious Studies 28 E MT22 Religious Studies 55 F MT22 Religious Studies 53 G MT22 Religious Studies 32 H MT22 Religious Studies 73 A MT22 Spanish 86 B MT22 Spanish 68 C MT22 Spanish 56 D MT22 Spanish 64 E MT22 Spanish 86 F MT22 Spanish 76 G MT22 Spanish 56 H MT22 Spanish 38 Hi Fowmy Is this what you mean? I have trimmed it down.
I have created a card easily enough to simply display one pupil's results average using slicers and filters.
I would like BI to tell me what quartile that average sits in based on the all the individual averages in the group. The table on the left is the raw data, the two smaller tables I calculated.
Pharms
Please find attached the file with the expected outcome:Quartile = VAR __Avg = [Student Average] VAR __Q1 = PERCENTILEX.EXC( ALLSELECTED( 'Table'[Pupil]) , [Student Average] , 0.25 ) VAR __Q2 = PERCENTILEX.EXC( ALLSELECTED( 'Table'[Pupil]) , [Student Average] , 0.50 ) VAR __Q3 = PERCENTILEX.EXC( ALLSELECTED( 'Table'[Pupil]) , [Student Average] , 0.75 ) RETURN SWITCH( TRUE(), __Avg > 0 && __Avg <= __Q1 , "Q1", __Avg > 0 && __Avg <= __Q2, "Q2", __Avg > 0 && __Avg <= __Q3 , "Q3", "Q4" )
