在 Power BI 中使用 TOPN 函数时的 ASC 参数 [英] ASC parameter when using TOPN function in Power BI

问题描述

我有这些数据:

然后有这个措施:

amount = SUM( play[amount] )

然后我尝试在这两个措施中使用 TOPN 函数的 ASC/DESC 参数:

Then I've tried to use the ASC/DESC arguments of the TOPN function in these two measures:

Top 2 customer per category ASC = 
VAR rnk = VALUES( play[customer] )

RETURN
CALCULATE(
    [amount],
    TOPN(
        2,
        ALL( play[customer] ),
        [amount],
        ASC
    ),
    RNK
)

Top 2 customer per category DESC = 
VAR rnk = VALUES( play[customer] )

RETURN
CALCULATE(
    [amount],
    TOPN(
        2,
        ALL( play[customer] ),
        [amount],
        DESC
    ),
    RNK
)

现在，如果我使用这两个度量，它看起来如下所示:

Now if I use these two measures it looks like the following:

发生了什么事?为什么度量 Top 2 customer per category ASC 没有显示任何内容?如何修改该度量以显示每个类别的底部两个值的值?

What is going on? Why is the measure Top 2 customer per category ASC showing nothing? How do I amend that measure so that it shows values for the bottom two values of each category?

推荐答案

这里的问题是TOPN的第二个参数应该是一个表，而不是一个未过滤的列.

The problem here is that the second argument of TOPN should be a table, not an unfiltered column.

不管 category 是什么，ALL(play[customer]) 都会返回表格:

Regardless of what the category is, ALL(play[customer]) returns the table:

customer
--------
xx
yy
zz
jj
qq
ff

度量 [amount] 仍然在 category 过滤器上下文中进行评估，但对于 category = "a" 你得到

The measure [amount] is still evaluated within the category filter context though so for category = "a" you get

customer  [amount]
------------------
xx          10
yy          12
zz          13
jj
qq
ff

对于 category = "b" 你得到

customer  [amount]
------------------
xx
yy
zz
jj          15
qq          16
ff           9

这些空白被认为比任何数字都小，因此当您对 ASC 进行排序时会选择它们.

These blanks are considered smaller than any number so they are what gets selected when you sort ASC.

试试这个稍作修改的措施:

Try this slightly modified measure instead:

Top 2 customer per category ASC =
VAR rnk = VALUES ( play[customer] )
RETURN
    CALCULATE (
        [amount],
        TOPN ( 2, CALCULATETABLE ( play, ALL ( play[customer] ) ), [amount], ASC ),
        RNK
    )

使用 CALCULATETABLE，category 过滤器上下文被保留.

Using CALCULATETABLE, the category filter context gets preserved.

附:要生成上面的表格，您可以编写一个新的计算表格，如下所示:

P.S. To generate the tables above you can write a new calculated table like this:

Top2Table =
CALCULATETABLE (
    ADDCOLUMNS ( ALL ( play[customer] ), "amount", [amount] ),
    play[category] = "a" <or "b">
)

这篇关于在 Power BI 中使用 TOPN 函数时的 ASC 参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！