问题描述
如何计算类别内的排名?假设我们有以下预期结果的示例数据:
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适用于类别的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!