在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:

这是怎么回事?为什么度量每个类别ASC排名前2位的客户什么都没显示?如何修改该度量，以使其显示每个类别的底部两个值?

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.

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

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

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

虽然仍在 category 过滤器上下文中评估度量 [amount] ，但是对于 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.要生成上面的表，您可以编写一个新的计算表，如下所示:

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屋！