每个类别的DAX等效于Excel PERCENTRANK.INC [英] DAX equivalent of Excel PERCENTRANK.INC per category

问题描述

我想在DAX中计算Excel函数PERCENTRANK.INC的等效项，但按类别进行计算。我承认我什至不知道如何计算类别。任何提示将不胜感激。

I would like to calculate in DAX equivalent of Excel function PERCENTRANK.INC but per Category. I admit that I do not know even how to calculate it for Category. Any hints will be highly appreciated.

以下是示例数据的M代码：

Here is M code for sample data:

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"

推荐答案

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

The measure below would produce the desired result. As there is no PERCENTRANK function in DAX, you can manually calculate it from the results of RANKX and 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
        )
    )
)

这篇关于每个类别的DAX等效于Excel PERCENTRANK.INC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！