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


Currently, I have the following table



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


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


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: <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


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



  • 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