I recently run into an issue when I needed to select the top 5 donors based on the contribution amount by occupation. The process is not that complex, however, for certain occupations, a large number of donors had the same amount. Therefore, a simple ranking method picked more than 5 donors based on the revenue alone. Below is one way to break the tie when ranking by the same value and then needing to pick top X donors.
The first step is to build a simple donations table with a few donors donating the same amount to replicate the actual problem. The code below builds a simple table for the exercise.
Donations =
UNION (
ROW ( “Donor ID”, INT ( 1 ), “Contribution”, CURRENCY ( 50 ) ),
ROW ( “Donor ID”, 2, “Contribution”, 40 ),
ROW ( “Donor ID”, 3, “Contribution”, 10 ),
ROW ( “Donor ID”, 4, “Contribution”, 10 ),
ROW ( “Donor ID”, 5, “Contribution”, 10 ),
ROW ( “Donor ID”, 6, “Contribution”, 10 ),
ROW ( “Donor ID”, 7, “Contribution”, 8 )
)
The next step is to test simple ranking and ranking using DENSE() tie break options. In the first case, donors 3-6 are ranked 3rd based on their revenue, then the donor with lower revenue after them is ranked 7th. Adding a DENSE() option still keeps donors 3-6 as number 3, however, the donor with lower revenue after them is ranked 4th.
Simple Ranking =
IF (
ISINSCOPE ( ‘Donations'[Donor ID] ),
RANKX ( ALL ( Donations ), [Donations],, DESC ),
BLANK ()
)
Dense Ranking =
IF (
ISINSCOPE ( ‘Donations'[Donor ID] ),
RANKX ( ALL ( Donations ), [Donations],, DESC, DENSE ),
BLANK ()
)
To break the tie, I am using the RAND() function (random number generated function). The function adds a small value between 1 and 0 (the solution might not work for very small numbers since it will throw off ranking) to the Donations column. The new column is called Donations No Ties. Then, I am using the new calculated column to rank donors based on the amount of the unique donation.
Donations No Ties = [Contribution] + RAND()
Ranking No Ties = RANKX( ALL(Donations), [Donations No Ties] )
Note that Ranking No Ties adds ALL to the Donations table. It is important to scan the whole Ranking No Ties adds ALL to the Donations table. It is important to scan the whole table based on the [Donations No Ties] column to receive an accurate result. If the table is affected by other filters, only a subset of the donors might be scored.
