Power BI:TopN 和所有其他 [英] Power BI: TopN and All Other

问题描述

我有一个类似于以下的数据集:

I have a data set that resembles the following:

Year    Location    Type    Amount
2015    West        Apple   12
2015    West        Pear    14
2015    East        Apple   55
2015    South       Orange  62
2015    West        Orange  64
2015    East        Banana  12
2015    North       Banana  23
2015    East        Peach   43
2015    East        Apple   89
2015    West        Banana  77
2015    West        Orange  43
2015    North       Apple   2

我需要对其进行汇总以显示 TopN 以及所有其他内容，以保持总计相同.仅过滤以仅显示 TopN 会减少总数并且不会起作用...

And I need it to be summarized to show TopN as well as all other in order to keep the grand total the same. Just filtering to show only the TopN reduces the grand total and will not work...

最终结果应如下所示(本例中为 n=3):

The end result should look like this (n=3 in this example):

Type         Amount
Orange       169
Apple        158
Banana       112
All Other    57
Grand Total  496

我已经创建了一个新的总量衡量标准:

I've gotten as far as creating a new measure for total amount:

Total_Amount = Sum(data[Amount])

但我不知道是继续使用 RankX 还是 TopN，而且我还没有在 Power BI 中找到一种简单的方法，不仅可以显示 TopN，而且还可以将属于所有其他"类别的所有其他内容分组.

But I don't know whether to proceed with RankX or TopN and I haven't found a straightforward way in Power BI to not only show the TopN, but also group everything else that would fall into the All Other category.

推荐答案

这可以通过创建一个排名度量来完成，然后使用它来确定前 N 个类型和后面的类型.

This can be done by creating a rank measure, then use it to determine the first N types and the succeeding ones.

创建此度量:

Total:=SUM(Data[Amount])

使用 [Total] 度量创建 [Type Rank] 度量:

Create the [Type Rank] measure using the [Total] measure:

Type Rank:=RANKX(ALL(Data[Type]);[Total])

现在使用 [Type Rank] 度量来确定何时聚合 [Amount].

Now use [Type Rank] measure to determine when aggregate the [Amount].

Top3:=IF ([Type Rank] <= 3;[Total];
  IF(HASONEVALUE(Data[Type]);
    IF(VALUES(Data[Type]) = "Others";
       SUMX ( FILTER ( ALL ( Data[Type] ); [Type Rank] > 3 ); [Total] )
    ) 
  ) 
)

将 3 的出现替换为您想要获取的 Types 的数量.另请注意，Data 是我的示例中的表名，您必须输入实际的表名.

Replace the 3 ocurrences by the number of Types you want to get. Also note Data is the name of the table in my example, you have to put the actual table name.

有必要将 Others 行添加到您的数据中，然后放置大于 N 类型的聚合，因此您可以使用以下内容:

It is necessary to add Others row to your data to then put the aggregation of the greather than N types, so you can use something like this:

Year    Location    Type    Amount
2015    West        Apple   12
2015    West        Pear    14
2015    East        Apple   55
2015    South       Orange  62
2015    West        Orange  64
2015    East        Banana  12
2015    North       Banana  23
2015    East        Peach   43
2015    East        Apple   89
2015    West        Banana  77
2015    West        Orange  43
2015    North       Apple   2
2015    East        Others  

这是我使用 Excel 中的数据创建的数据透视表:

This is a pivot table I've created using your data in Excel:

这是类型列中每个值的计算排名:

This is the calculated rank for every value in the Types column:

这种方法也可以在 Power BI 中使用.

This approach can be used in Power BI too.

如果这对你有帮助，请告诉我.

Let me know if this helps you.

这篇关于Power BI:TopN 和所有其他的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！