powerbi - 每个类别相当于 Excel PERCENTRANK.INC 的 DAX

Question

我想在 DAX 中计算等效于 Excel 函数 PERCENTRANK.INC 但每个类别。我承认我什至不知道如何计算类别。任何提示将不胜感激。

这是示例数据的 M 代码：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcisqzSwpVtJRSiwoyEkF0oZKsTpIwkmJeUAIZJigipfn56QlpRYVVQLZpqhSyRlQcWOweFhqempJYlJOKlgusagovwTIMMKUK8gvSSzJhzsBRS4/LzM/D0ibo1qFw9HILogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, Product = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", Int64.Type}})
in
    #"Changed Type"

标签： powerbidax

Answer 1

下面的措施将产生预期的结果。由于 DAX 中没有 PERCENTRANK 函数，您可以根据 RANKX 和 COUNTROWS 的结果手动计算。

Percent Rank Within Category = 
IF (
    -- This calculation only makes sense if there is only one product
    -- in the current filter context. If there are more than one products
    -- or no product in the filters, BLANK should be returned.
    HASONEVALUE ( MyTable[product] ),

    -- Get all products which belong to the same parent category with
    -- the product currently being filtered
    VAR tbl = CALCULATETABLE (

        -- all products, in the modified filter context of...
        VALUES ( MyTable[product] ),

        -- no filter on product
        REMOVEFILTERS ( MyTable[product] ),

        -- and under the same parent category
        VALUES ( MyTable[Category] )
    )

    RETURN
    CALCULATE (
        -- PERCENTRANK = (<rank of product> - 1)
        --               / (<total N of products> - 1)
        DIVIDE (

            -- Sales rank of each product in ascending order
            RANKX (
                tbl,
                CALCULATE ( SUM ( MyTable[Amount] ) ), ,
                ASC
            ) - 1,

            -- Total number of products
            COUNTROWS ( tbl ) - 1,

            -- When there is only one product, it should return 1
            1
        )
    )
)