DAX Ranking

[robarivas]

Two Questions:

1. Is there a scientific basis for determing which ranking methodology is best to use when you have ties or is it purely nothing more than a matter of taste/preference? The items I will be ranking will have a different rank per period and I want to optimize on the lowest median rank over multiple periods with a limiting factor that will prevent me from simply choosing the top n items (sorted by median). I am aware of the following methods that deal with ties:
   
   • Standard competition ranking ("1224" ranking)
   • Modified competition ranking ("1334" ranking)
   • Dense ranking ("1223" ranking)
   • Fractional ranking ("1 2.5 2.5 4" ranking)
   • (so far I've only managed to build columns in Power BI for Modified Competition and Dense)
2. What is the DAX formula/syntax for Fractional ranking? In addition to the value column I'm ranking the individual items on, I want to limit the ranking to be within the following two groups: Period and Category. Below is a hypothetical example (let's say in this case that the limiting factor is 1,400 to spend per month across all categories):

Period          Category          Item              Price ($)      Fractional Rank

May             Bikes             BikeX2            578            1

May             Bikes             BikeY6            560            2

May             Bikes             BikeN7            558            3.5

May             Bikes             Bike J9           558            3.5

May             Bikes             BikeD3            479            5

May             Toys              ToyF3             592            1

May             Toys              ToyK7             588            2

May             Toys              ToyW1             468            3

May             Toys              ToyE8             402            4.5

May             Toys              ToyR4             402            4.5

Jun             Bikes             BikeJ9            642            1.5
Jun             Bikes             BikeD3            642            1.5
Jun             Bikes             BikeN7            541            3
Jun             Bikes             BikeX2            506            4
Jun             Bikes             BikeY6            479            5
Jun             Toys              ToyW1             483            1
Jun             Toys              ToyF3             472            2.5
Jun             Toys              ToyR4             472            2.5
Jun             Toys              ToyE8             399            4
Jun             Toys              ToyK7             389            5

[OwenAuger · Accepted solution]

Hi @robarivas,

Don't know the answer to 1.

For 2, I'm assuming you want to do this in a calculated column.

You can calculate the Fractional Rank by calculating the Standard Competition ranking and adding 0.5 * (Num items with same price - 1).

Also, you can use ALLEXCEPT to calculate ranking within the current Period/Category group.

In DAX, this looks something like:

Fractional Rank = 
VAR NumTies =
    CALCULATE (
        COUNT ( Price[Price] ), // Or COUNTROWS ( Price )
        ALLEXCEPT ( Price, Price[Period], Price[Category], Price[Price] )
    )
RETURN
    RANKX (
        CALCULATETABLE ( Price, ALLEXCEPT ( Price, Price[Period], Price[Category] ) ),
        Price[Price]
    )
        + DIVIDE ( NumTies - 1, 2 )

Here's a sample pbix file.

Owen 🙂

[Anonymous]

In divide function you have used NumTies ? What it is ?

[robarivas]

That formaula/syntax worked. Thanks so much!! 

[Greg_Deckler]

Not aware of a way to do this with just the standard RANKX funtion. RANKX supports skip and dense for handling ties. Skip is the equivalent of your Standard. I would imagine it would involve a RANKX using standard for ties and then the Fractional ranking built off of this standard ranking.