计算列中按类别划分的DAX PERCENTILE.INC [英] DAX PERCENTILE.INC by category in calculated column

问题描述

我想在计算列中使用PERCENTILE.INC按类别计算四分位数(Ntile)。我的问题是这个问题的变体： Equal bins in DAX equivalent of NTILE function

如果要按类别计算结果，如何修改问题的接受解决方案？

Bucket = 
VAR N = 4
VAR Percentiles =
    ADDCOLUMNS (
        GENERATESERIES ( 1, N ),
        "Percentile", PERCENTILE.INC ( Table1[Col1], [Value] / N )
    )
RETURN
    MINX ( FILTER ( Percentiles, Table1[Col1] <= [Percentile] ), [Value] )

我尝试了此操作，但结果不是所需的值：

VAR N = 4
VAR Percentiles =
    ADDCOLUMNS(
        CROSSJOIN( VALUES( Tab[Category] ), GENERATESERIES( 1, N ) ),
        "Percentile",
            PERCENTILEX.INC(
                VAR Category = 'Tab'[Category] RETURN FILTER( Tab, 'Tab'[Category] = Category ),
                Tab[Quantity] * 1.0,
                [Value] / N
            )
    )
RETURN
    MINX(
        FILTER(
            Percentiles,
            Tab[Quantity] <= [Percentile]
                && 'Tab'[Category] = [Category]
        ),
        [Value]
    )

编辑。

示例数据：

Table = 
DATATABLE (
    "No", INTEGER,
    "Category", STRING,
    "Quantity", DOUBLE,
    {
        {  1 , "apple"  , 1 },
        {  2 , "apple"  , 5 },
        {  3 , "apple"  , 1 },
        {  4 , "apple"  , 4 },
        {  5 , "apple"  , 1 },
        {  6 , "apple"  , 2 },
        {  7 , "apple"  , 5 },
        {  8 , "apple"  , 4 },
        {  9 , "banana" , 9 },
        { 10 , "banana" , 7 },
        { 11 , "banana" , 6 },
        { 12 , "banana" , 4 },
        { 13 , "banana" , 5 },
        { 14 , "banana" , 7 },
        { 15 , "banana" , 8 },
        { 16 , "banana" , 9 }
    }
)

推荐答案

这是我在上一个帖子的评论中建议的解决方案。

Bucket =
VAR N = 4
VAR Percentiles =
    ADDCOLUMNS (
        GENERATESERIES ( 1, N ),
        "Percentile",
            VAR K = [Value] / N
            RETURN
                CALCULATE (
                    PERCENTILE.INC ( Table1[Col1], K ),
                    ALLEXCEPT ( Table1, Table1[Category] )
                )
    )
RETURN
    MINX ( FILTER ( Percentiles, Table1[Col1] <= [Percentile] ), [Value] )

这篇关于计算列中按类别划分的DAX PERCENTILE.INC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！