类别内的 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?

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.

解决方案

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.

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.

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屋！