powerbi - DAX RANKX for within Category

Question

How to calculate ranking for within Category? Say we have sample data with the following expected results:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcisqzSwpVtJRSiwoyEkF0oZgHKuDJJWUmAeEQIYJEBuhypXn56QlpRYVVQLZpkBsjCqdnAGVMwNrB8mFpaanliQm5aSC5AvySxJL8lGsRZFPTiwqyi8BWwuzGkU+Py8zPw9Im0OsjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, Subcategory = _t, Sales = _t, Results = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Subcategory", type text}, {"Sales", Int64.Type}, {"Results", Int64.Type}})
in
    #"Changed Type"

I have roamed for hints there. Based on the pattern shown there, I was able to cook the following code:

Rank within category =
RANKX (
    FILTER (
        ALL (
            'MyTable'[Category],
            'MyTable'[Subcategory]
        ),
        'MyTable'[Category]
            = MAX ( 'MyTable'[Category] )
    ),
    CALCULATE (
        SUM ( 'MyTable'[Sales] )
    )
)

The code above produces expected results but I have no idea how it works. Can you please shed light on that?

Update.

I have found here an alternative simple approach which has elegant few lines, but again, the logic how it works remains a puzzling riddle for me. Can you explain it?

Rank within category using variables = 

VAR TotalSalesThisItem = [SalesMeasure] // a variable to hold each item's sales

// now count how many items have sales which match or exceed this
RETURN
    COUNTROWS (
        FILTER (
            ALL ( MyTable[Subcategory] ),
            [SalesMeasure] >= TotalSalesThisItem
        )
    )

The mystique thing about this code is how it knows what is the Category? Code mentions only Subcategory column. Yet it produces expected results.

Answer 1

To be honest, that seems very complicated and unintuitive to accomplish what you want. I've recreated your table (thanks for including the M code!) and wrote the following calculated table:

Ranked =
RANKX (
    FILTER (
        Table5,
        Table5[Category]
            = EARLIER ( Table5[Category] )
    ),
    Table5[Sales],
    ,
    ASC,
    DENSE
)

This gives the same output as your Results column. What is happinging here is that I am giving it a altered table, based on a FILTER statement where I am limiting the RANKX operation to all rows of that Category. EARLIER() refers to the column one level up in the calculation (it is evaluated for each row, with EARLIER you can reference a different column on that row).

If this helps you, please mark it as the solution :)