Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
Could you please help me with a measure that can rank the zero values based on another column.
Sample dataset:
Student Name | Attendence (%) | Total Mark | Expected Ranking |
Ian | 70 | 50 | 1 |
Becky | 75 | 48 | 2 |
Cathy | 80 | 38 | 3 |
John | 50 | 38 | 4 |
Denny | 60 | 20 | 5 |
Kin | 65 | 10 | 6 |
Lena | 63 | 7 | 7 |
Mark | 72 | 3 | 8 |
Ean | 90 | 0 | 9 |
Nora | 80 | 0 | 10 |
Fahl | 70 | 0 | 11 |
The column "Expected Ranking" is the outcome of a measure that ranks the students based on the "Total Mark" column. But when it comes to the zero marks it should rank based on the "Attendance(%)" column.
To summarize, whenever there is a tie in marks, it should consider the Attendance column and rank accordingly.
(Row 3 and 4 are tie and it ranked based on Attendance column)
Any help on this appreciated.
Thank you.
Solved! Go to Solution.
Ranking =
VAR _mark = [Total Mark]
RETURN
COUNTROWS (
FILTER (
ALLSELECTED ( Table[Student Name] ),
[Total Mark] >= _mark
&& IF (
[Total Mark] = _mark,
'Table'[Student Name] <= MAX ( 'Table'[Student Name] ),
TRUE ()
)
)
)
Ranking =
VAR _mark = [Total Mark]
RETURN
COUNTROWS (
FILTER (
ALLSELECTED ( Table[Student Name] ),
[Total Mark] >= _mark
&& IF (
[Total Mark] = _mark,
'Table'[Student Name] <= MAX ( 'Table'[Student Name] ),
TRUE ()
)
)
)
Thanks for the reply.
Is [Total Mark] a measure that is the sum of the Total Marks column?
it's a measure
=SUM(Table[Mark])
Thank you. That seems to work!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |