powerbi - DAX RANKX用于子类别(DAX RANKX for Subcategory)

Question

How to calculate ranking for subcategory?

(如何计算子类别的排名？)

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"

Calculating Rank for on column is a snap:

(快速计算列上的排名：)

RankSubcategory =
RANKX (
    ALL ( MyTable[Subcategory] ),
    [SalesMeasure],
    ,
    ASC,
    SKIP
)

I have roamed for hints there:

(我漫游了那里的提示：)

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

(https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures)

I got the following code:

(我得到以下代码：)

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

It give expected results.

(它给出了预期的结果。)

I have no idea how it works.

(我不知道它是如何工作的。)

Can you please shed light on that?

(你能说明一下吗？)

  ask by Przemyslaw Remin translate from so

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:

(我已经重新创建了您的表（感谢包含M代码！）并编写了以下计算表：)

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.

(这里的障碍是，我根据FILTER语句为它提供了一个经过修改的表，其中我将RANKX操作限制为该类别的所有行。)

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).

(EARLIER（）指的是计算中上一级的列（将对每一行进行评估，使用EARLIER可以在该行上引用不同的列）。)

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

(如果这对您有帮助，请将其标记为解决方案:))