Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |