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 peopl...

@quartiles

Fowmy

2 years ago

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

Students Quartile.pbix20 KB

Pharms

Frequent Visitor

2 years ago

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.

Fowmy

Super User

2 years ago

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

Students Quartile.pbix20 KB

Forum Discussion

Ranking averages into quartiles

Related content

quartile ranking

DAX Measure for Ranking the valid rows and calculating the average rating

Quartiles and average per quartile

Duplicate ranking

Rank with conditions