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