Sorting portfolios based on criteria (top30%,Middle 40%. and Bottom 30%)

Question

Currently, I have the following table

Company---------Date--------Exchange-------Size

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

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

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

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

I want to allocate each company into three categories

"Top" ==> Top 30%

"Middle" ==> Middle 40%

"Bottom" ==> Bottom 30%

Calculating cutoff values should be filtered with 'year' and 'Exchange'=A

I have tried the following formula

  =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"))

For some reasons, It is not working as it should be.

Thanks in advance.

Stephan · Answer 1 · 2016-07-11T16:03:44.460

0

The DAX (a.k.a. Power Pivot) formula PERCENTILE() is only available in the preview edition of Excel 2016 (see here: https://msdn.microsoft.com/en-us/library/dn802551.aspx).

Since I don't have this version installed I can't give an answer using the suggested formula. However, you can build your own percentile logic using RANKX().

First, add a new column called [RankInExchangeA]:

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

The EARLIER() function basically means ThisRowsValue().

Now add your desired Top/Bottom/Middle column:

= 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"
     )

The *(3/10) and *(7/10) part of the formula determines the thresholds. Note that in the example the clustering is done as follows:

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

edited Jul 11 '16 at 16:03

answered Jul 11 '16 at 15:09

Stephan

650
7
16

thank you for your help. I think I did not formulate the question correctly, I want to allocate all the companies (Companies are in other exchanges as well). But all companies have to be allocated based on the cut off values calculated from only companies in exchange A. – Myurathan Kajendran Jul 11 '16 at 15:53
I think RankInExchangeA column should have a filter for Exchenge=A. And Switch formula should compare with size value. I'm trying to reproduce median with this method, so that I can compare the results. But no luck yet. For median I multiplied by 0.5 – Myurathan Kajendran Jul 11 '16 at 16:32
You want to determine the thresholds using a subset of the data and evaluate against the determined threshold using your full dataset. You can't do this in a single step. You'll need to determine the thresholds first, store them somehow as variable and compare to them afterwards. This link explains how to achieve your goal by using measures instead of calc. columns: http://www.powerpivotpro.com/2011/09/creating-accurate-percentile-measures-in-dax-%E2%80%93-part-i/ However, I would recommend getting familiar with the proper usage of measures in general first. – Stephan Jul 11 '16 at 16:52
I was thinking the same. Thank you so much for your assistant. It means a lot. – Myurathan Kajendran Jul 11 '16 at 17:29
I have done it using prentilex function. and your switch function helped a lot. Can you please look into this power query question. I want to improve my calculating power by reducung the sample size. http://stackoverflow.com/questions/38500927/how-to-filter-rows-in-a-table-based-on-values-in-another-table-in-power-query – Myurathan Kajendran Jul 21 '16 at 10:28

score 0 · Accepted Answer · answered Jul 13 '16 at 14:58

I have manged to do it with percentilex.inc function. Here is the full formula that I have used.

=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")

Sorting portfolios based on criteria (top30%,Middle 40%. and Bottom 30%)

2 Answers2

Linked