Power BI:TopN和所有其他 [英] Power BI: TopN and All Other
问题描述
我有一个类似于以下内容的数据集:
年份位置类型金额
2015西苹果12
2015西梨14
2015东苹果55
2015南橙62
2015西橙64
2015东香蕉12
2015北香蕉23
2015东桃43
2015东苹果89
2015西香蕉77
2015西橙43
2015北苹果2
为了使总计保持不变,我需要对其进行汇总以显示TopN以及其他所有内容。只是过滤以仅显示TopN会减少总数,并且将不起作用...
最终结果应如下所示(在此示例中,n = 3):
类型金额
橙色169
苹果158
香蕉112
所有其他57
总计496
我已经为总金额创建了一个新度量:
Total_Amount = Sum(data [Amount])
但是我不知道是继续使用RankX还是TopN,而且我还没有在Power BI中找到一种简单的方法不仅可以显示TopN,还可以对其他所有东西进行分组
这可以通过创建排名度量,然后使用它来确定
创建此度量:
总计:= SUM(数据[金额])
使用 [总计]
度量创建 [类型等级]
度量:
类型等级:= RANKX(ALL(Data [Type]); [Total])
现在使用 [类型等级]
来确定何时汇总 [Amount]
。
Top3:= IF([类型排名]< = 3; [总计];
IF(HASONEVALUE(Data [Type]);
IF(VALUES(Data [Type])=其他;
SUMX(FILTER(ALL(Data [Type])); [Type排名]> 3); [总计])
)
)
)
将 3
次出现替换为要获取的 Types
的数量。还要注意,在我的示例中, Data
是表的名称,您必须输入实际的表名。
有必要在数据中添加 Others
行,然后对大于 N
类型的聚合进行汇总,因此您可以使用类似这样的东西:
年份位置类型金额
2015西苹果12
2015西梨14
2015东苹果55
2015南橙62
2015西橙64
2015东香蕉12
2015北香蕉23
2015东桃子43
2015东苹果公司89
2015西香蕉77
2015西橙子43
2015北苹果2
2015东其他
这是我使用Excel中的数据创建的数据透视表:
这是计算类型列中每个值的排名:
这种方法也可以在Power BI中使用。
让我知道这是否对您有帮助。 / p>
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
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...
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])
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.
This can be done by creating a rank measure, then use it to determine the first N types and the succeeding ones.
Create this measures:
Total:=SUM(Data[Amount])
Create the [Type Rank]
measure using the [Total]
measure:
Type Rank:=RANKX(ALL(Data[Type]);[Total])
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] )
)
)
)
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.
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
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:
This approach can be used in Power BI too.
Let me know if this helps you.
这篇关于Power BI:TopN和所有其他的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!