Consider the following dataset within a Power pivot data sheet
The above datasheet shows the details of students.
For simplicity, we consider only four columns ID, Name, Subject, Marks
The above illustration shows a calculated column RANK being added which ranks the data based on descending order of Marks for each subject.
Now lets analyze the formula for this calculated column which looks like below.
It can be written in two ways
=RANKX(FILTER(StudentTable,EARLIER(StudentTable[Subject])=StudentTable[Subject]),StudentTable[Marks])
=CALCULATE(COUNTROWS(StudentTable)+1,FILTER(StudentTable,EARLIER(StudentTable[Subject])=StudentTable[Subject] && EARLIER(StudentTable[Marks]) < StudentTable[Marks]))
The formula works as follows
FILTER function provides a table resultset by applying the condition subject = current rows subject and marks < current row marks. Taking a count of this and adding + 1 using CALCULATE gives the position of current row within table and same subject group based on descending order of Marks which is the RANK. EARLIER function is used here for taking current row as the context and then compare the marks value to previous rows to get its relative position within the subject group.
Now lets validate this by means of a corresponding T-SQL query.
Creating a similar table and applying RANK formula gives us below result.
DECLARE @Students table
(
ID int,
Name varchar(100),
[Subject] varchar(20),
Marks int
)
INSERT @Students
VALUES
(1, ‘Praveen’, ‘Maths’, 98),
(3, ‘Mohan’, ‘Physics’, 88),
(5, ‘Mary’, ‘Maths’, 92),
(9, ‘Mathew’, ‘Physics’, 90),
(11, ‘Prithi’, ‘Maths’, 85),
(17 ,’Mithun’, ‘Maths’, 77),
(23, ‘Martin’, ‘Physics’, 82),
(25, ‘Kiran’, ‘Maths’, 93),
(27, ‘Kumar’, ‘Chemistry’ ,75),
(30, ‘Sita’, ‘Physics’, 88),
(32, ‘Jophin’, ‘Chemistry’, 62),
(33, ‘Gladis’, ‘Chemistry’, 83),
(35, ‘Kunal’, ‘Chemistry’, 88),
(36, ‘Manu’, ‘Physics’ ,93),
(40, ‘Madhur’, ‘Chemistry’ ,91)
SELECT *,
RANK() OVER (PARTITION BY [Subject] ORDER BY Marks DESC) AS [Rank]
FROM @Students
ORDER BY ID
The output is as shown
Compare this to the earlier output and we will see that it exactly matches the calculated column values we created using DAX in Power pivot.
