48086

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.

Answer1:

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

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

Answer2:

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

Recommend

  • Calculating the standard deviation from columns of values and frequencies in Power BI
  • Debug using Rubymine
  • SQL: Count of rows since certain value first occurred: keep counting
  • Use Windows Powershell ISE for Exchange when you have MFA
  • Optimizing Dax & model for “where date between” type queries
  • Power BI: Percent Change Formula
  • run jekyll --server failed in win7
  • Train neural network with sine function
  • Why does this GLSL shader work fine with a GeForce but flickers strangely on an Intel HD 4000?
  • C# / ActionScript with AxShockwaveFlash
  • Spark dataframe to nested map
  • How to modify search result page given by Solr?
  • how to insert a new column in a dataset with values if it satisfies a statement
  • Convert adjacency matrix to a csv file
  • c# linear regression given 2 sets of data
  • Is there a way to directly consume a Rayon chain without collecting it first?
  • How To Delete All Words After X Characters
  • Transpose table then set and rename index
  • Removing event listeners on automatically created multiple elements
  • css calendar - td background diagonal split - two colors
  • Selectively hide background elements when overlayed with transparent div
  • Segmentation Fault on MySQL2 / Ruby 1.9.3 / Rails 3.2
  • Can my PDF ping my server when it is opened?
  • Access user's phone number on iOS 7
  • GAE: Way to get reference to an HttpSession from its ID?
  • How to modify the colors in the legend of a plot using a fill gradient?
  • Mysterious problem with floating point in LISP - time axis generation
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • How can I estimate amount of memory left with calling System.gc()?
  • Rearranging Cells in UITableView Bug & Saving Changes
  • R: gsub and capture
  • Circular dependency while pushing http interceptor
  • Matrix multiplication with MKL
  • Linker errors when using intrinsic function via function pointer
  • Benchmarking RAM performance - UWP and C#
  • Hits per day in Google Big Query
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • File not found error Google Drive API
  • Converting MP3 duration time
  • Net Present Value in Excel for Grouped Recurring CF