Dividing tied RANKs using MDX

Dividing tied RANKs using MDX

Still getting the hang of interpreting the MDX documentation on MSDN. So
for the RANK function it has the following:
If a numeric expression is specified, the Rank function determines the
one-based rank for the specified tuple by evaluating the specified numeric
expression against the tuple. If a numeric expression is specified, the
Rank function assigns the same rank to tuples with duplicate values in the
set. This assignment of the same rank to duplicate values affects the
ranks of subsequent tuples in the set. For example, a set consists of the
following tuples, {(a,b), (e,f), (c,d)}. The tuple (a,b) has the same
value as the tuple (c,d). If the tuple (a,b) has a rank of 1, then both
(a,b) and (c,d) would have a rank of 1. However, the tuple (e,f) would
have a rank of 3. There could be no tuple in this set with a rank of 2. If
a numeric expression is not specified, the Rank function returns the
one-based ordinal position of the specified tuple. The Rank function does
not order the set.
In the following script if two people are tied second for the highest
salary I get the following salary ranks:
1
2
2
4
What I'd like to do is use the number of years in service to decide which
of the tied people has the higher rank. Is this possible?
WITH
SET [OrderedPeople] AS
ORDER(
NONEMPTY(
[PeopleDimension].[PeopleHier].[NamesLevel].members,
{ //following means if one or the other is null
//then the row is not excluded
[Measures].[Salary],
[Measures].[NumYearsService]
}
),
[Measures].[Salary]
*
[Measures].[NumYearsService]
,
BDESC
)
MEMBER [Measures].[Salary_Rank] AS
RANK([PeopleDimension].[PeopleHier].CurrentMember,
[OrderedPeople],
[Measures].[Salary] //<<<how do I use numYearsService to
decide ties?
)
SELECT
NON EMPTY
{
[Measures].[NumYearsService],
[Measures].[Salary],
[Measures].[Salary_Rank]
}
ON COLUMNS,
NON EMPTY
[OrderedPeople]
ON ROWS
FROM [ourCube]
WHERE
(
{TAIL([Date].[Date - Calendar Month].[Calendar Day],7)(0):
TAIL([Date].[Date - Calendar Month].[Calendar Day],7)(6)}
)