T-SQL to DAX : Simulating PERCENT_RANK() analytic function

Recently I was involved in a project which required rewriting few logic from T-SQL using DAX functions for migrating values to a Power pivot based model. The DAX function set being not as comprehensive as T-SQL required me to simulate some T-SQL functions in DAX using the natively available DAX function. I’m starting a series of blog posts to share some of the conversion logic for simulating T-SQL advanced analytical functions using DAX.
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.