powerbi - 每个类别的DAX等效于Excel PERCENTRANK.INC(DAX equivalent of Excel PERCENTRANK.INC per category)

Question

I would like to calculate in DAX equivalent of Excel function PERCENTRANK.INC but per Category.

(我想在DAX中计算Excel函数PERCENTRANK.INC的等效项，但按类别进行计算。)

I admit that I do not know even how to calculate it for Category.

(我承认我什至不知道如何计算类别。)

Any hints will be highly appreciated.

(任何提示将不胜感激。)

Here is M code for sample data:

(这是示例数据的M代码：)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcisqzSwpVtJRSiwoyEkF0oZAbKAUq4MklZSYB4RAhglITs/Y2BhVvjw/Jy0ptaioEsg2BSsxMzNDVZKcAZU3A1sBkgtLTU8tSUzKSQXLJxYV5ZcAGUYoVqCoKcgvSSzJR3Eiinx+XmZ+HpA2h1gRCwA=", 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", type number}})
in
    #"Changed Type"

  ask by Przemyslaw Remin translate from so

Answer 1

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.

(由于DAX中没有PERCENTRANK函数，因此您可以根据RANKX和COUNTROWS的结果手动进行计算。)

Percent Rank Within Category = 
IF (
    HASONEVALUE ( Sales[Subcategory] ),
    VAR Subcategories = CALCULATETABLE (
        VALUES ( Sales[Subcategory] ),
        REMOVEFILTERS ( Sales[Subcategory] ),
        VALUES ( Sales[Category] )
    )
    RETURN
    CALCULATE (
        DIVIDE (
            RANKX (
                Subcategories,
                CALCULATE ( SUM ( Sales[Sales] ) ), ,
                ASC
            ) - 1,
            COUNTROWS ( Subcategories ) - 1
        )
    )
)