DAX RANKX适用于类别 [英] DAX RANKX for within Category

问题描述

如何计算类别内的排名？假设我们有以下预期结果的示例数据：

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?

更新。

我发现了此处另一种简单的方法，几乎​​没有几行内容，但是再次讲，它的工作原理仍然令我感到困惑。您能解释吗？

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.

推荐答案

您的解决方案（第一个命名为[类别内的排名]的解决方案）将仅作为度量，而不作为计算列。

Your solution (the first one named [Rank within category]) will only work as a measure and not as a calculated column. It uses the filtercontext generated by the table visual.

Ranxx函数的第一个参数返回完整表的该部分，其中类别与其中的选定值相同。视觉效果（MAX（'MyTable'[Category]）），因为MAX函数不受ALL函数的影响。

The first paramater of the Rankx function returns that part of the complete table where the category is the same as the selected value in the visual (MAX ( 'MyTable'[Category] )) because the MAX-function is not affected by the ALL-function.

然后，第二个参数（每行中的[sales]）将按返回表中的[sales]值排序。您需要CALCULATE函数才能使用FILTER函数创建的filtercontext。

Then the second parameter (the [sales] in each row) will be ranked to the [sales] values in the returned table. You need the CALCULATE Function to use the filtercontext created by FILTER function.

这将是一种更清洁，更短的解决方案：

This would a be cleaner and shorter way for your solution:

Rank within category =
RANKX (
    ALLEXCEPT ( MyTable, MyTable[Category] ),
    CALCULATE ( SUM ( 'MyTable'[Sales] ) )
)

这篇关于DAX RANKX适用于类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！