每个类别相当于 Excel PERCENTRANK.INC 的 DAX [英] 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
        )
    )
)

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