We will start series by taking PERCENT_RANK function available in SQL 2012.
This function will provide you with relative rank of a row within a group of rows. The calculation behind can be given as
PERCENT_RANK() = (RANK() – 1) / (Total Rows – 1)
Now we can see how we will simulate the same in DAX
The simulation for the RANK function can be found from below blog post.
http://visakhm.blogspot.in/2013/07/t-sql-to-dax-simulating-rank-function.html
Based on above logic we create a calculated column called Rank and using it we can create a calculated column for PERCENT_RANK as below
=(StudentTable[Rank]-1)/(COUNTROWS(FILTER(StudentTable,EARLIER(StudentTable[Subject])=StudentTable[Subject]))-1)
Now lets see the result
The result can be validated by means of corresponding T-SQL query as below
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],
PERCENT_RANK() OVER (PARTITION BY [Subject] ORDER BY Marks DESC) AS PercentRank
FROM @Students
ORDER BY ID
The result matches with what we got earlier to indicate that the DAX simulation we applied is correct.