In our previous post we saw how to identify the top N Sales Persons a common follow up to this task it to rank the Sales Persons as well. In this post we explore how to RANK the Sales person based on the total Sales Amount.
Ranks = RANKX(ALL('Dimension Employee'[Employee]),SUMX(RELATEDTABLE('Fact Order'),'Fact Order'[Total Including Tax]))The above formula shows how to Rank Employees from the Employee Dimension to the Related Fact table Fact Order based on the sum of Total Including Tax Column. The end result will be as shown below
The sum of total Incld Tax has been provided in order to validate that ranking has been performed correctly.
The SQL Equivalent of the above DAX function would be similar to
SELECT e.Employee
,sum(o.[Total Including Tax])
,rank() OVER ( ORDER BY sum(o.[Total Including Tax]) DESC )
FROM Dimension.Employee e
LEFT OUTER JOIN fact.[order] o ON o.[Salesperson Key] = e.[Employee Key]
GROUP BY e.Employee
Please Consider Subscribing
