topic Re: Ranking averages into quartiles in DAX Commands and Tips

Ranking averages into quartiles

Pharms — Sun, 10 Dec 2023 14:37:44 GMT

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.

Re: Ranking averages into quartiles

Fowmy — Sun, 10 Dec 2023 19:27:22 GMT

Pharms

Could you share the expected result in Excel as you have share the source data?

Re: Ranking averages into quartiles

Pharms — Sun, 10 Dec 2023 23:02:55 GMT

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.

Re: Ranking averages into quartiles

Fowmy — Mon, 11 Dec 2023 19:46:12 GMT

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" )