根据标准(前30%，中间40%和后30%)对投资组合进行排序

Question

目前，我有下表

公司---------日期--------交易所-------规模

A---------------2000---------A-------------50

A---------------2001---------A------------ 100

B---------------2000---------B------------450

B---------------2001---------B------------- 458

我想将每家公司分为三类

"顶部" ==> 顶部 30%

"中间" ==> 中间 40%

"底部" ==> 底部 30%

计算截止值应使用"年"和"交换"=A 进行过滤

我尝试了以下公式

  =if([B/M]>PERCENTILEX.INC(FILTER(June,June[exchg]="A"&&EARLIER([datadate])=[datadate]),June[B/M],0.3),"L",IF([B/M]>PERCENTILEX.INC(FILTER(June,June[exchg]="A"&&EARLIER([datadate])=[datadate]),June[B/M],0.7),"M","T")) 

由于某些原因，它无法正常工作。

提前谢谢。

Answer 1

DAX（又名Power Pivot）公式PERCENTILE()仅在Excel 2016的预览版中可用（请参阅此处：https://msdn.microsoft.com/en-us/library/dn802551.aspx）。

由于我没有安装此版本，因此无法使用建议的公式给出答案。但是，您可以使用 RANKX() 构建自己的百分位数逻辑。

首先，添加一个名为 [RankInExchangeA] 的新列：

=RANKX(
    FILTER(
        Table1,
        EARLIER([Exchange])=[Exchange] && EARLIER([year])=[year]
    ),
    [size]
 )

EARLIER()函数基本上意味着 ThisRowsValue() .

现在添加所需的顶部/底部/中间列：

= Switch(TRUE(),
         [RankInExchange]>=CALCULATE(max([RankInExchange]),filter(Table1,EARLIER([year])=[year] && EARLIER([Exchange])=[Exchange]))*(7/10),"Bottom",
         [RankInExchange]<CALCULATE(max([RankInExchange]),filter(Table1,EARLIER([year])=[year] && EARLIER([Exchange])=[Exchange]))*(3/10),"Top",
         "Middle"
     )

公式的*(3/10)和*(7/10)部分确定阈值。请注意，在示例中，聚类分析按如下方式完成：

Bottom <= 30% < Middle <= 70% < Top

Answer 2

我已经用percentilex.inc函数来做这件事了。这是我使用的完整公式。

=SWITCH(TRUE(),Table[SIZE]<=PERCENTILEX.INC(FILTER(Table,EARLIER([Date])=[Date]),Table[SIZE],0.3),"Bottom",Table[SIZE]>=PERCENTILEX.INC(FILTER(Table,EARLIER([Date])=[Date]),Table[SIZE],0.7),"Top","Middle")