Excluding Top N% of values from calculated column

[rjafri2015]

Hi,

I am looking for a way to exclude the top 10% and bottom top 10% of values by category for a calculated column. 

Here is some sample data.

The "Regional CY MTD" and "Regional LY MTD" columns are the sum of CY MTD and LY MTD for all customers in those respective regions. Here is the code for the calcuation. 

Regional CY MTD = CALCULATE(SUM('Table '[Revenue]), DATESMTD('Table - Calendar'[Date]), ALLEXCEPT('Table ','Table'[Sales Region Name]))

What I would like to do is to exclude the top 10% and bottom 10% of accounts based on the "YoY MTD Diff" column when calculating Regional CY MTD and Regional LY MTD. 

[ahadkarimi]

Try these 4 DAX,  you got any questions or need more info, just hit me up!

YoY MTD Rank =
RANKX( FILTER( 'Table', 'Table'[Sales Region Name] = EARLIER('Table'[Sales Region Name])),
'Table'[YoY MTD Diff],, ASC,DENSE)
---------------
Total Accounts =
CALCULATE( COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[Sales Region Name]))
----------------

Regional CY MTD (Filtered) =
CALCULATE(SUM('Table'[CY MTD]),
FILTER('Table','Table'[YoY MTD Rank] > (0.1 * [Total Accounts]) &&'Table'[YoY MTD Rank] <= (0.9 * [Total Accounts])),DATESMTD('Table - Calendar'[Date]),ALLEXCEPT('Table', 'Table'[Sales Region Name]))

----------------
Regional LY MTD (Filtered) =
CALCULATE(SUM('Table'[LY MTD]),FILTER('Table', 'Table'[YoY MTD Rank] > (0.1 * [Total Accounts]) &&
'Table'[YoY MTD Rank] <= (0.9 * [Total Accounts])),DATESMTD('Table - Calendar'[Date]),
ALLEXCEPT('Table', 'Table'[Sales Region Name]))

[TheoC]

Hi @rjafri2015 

I believe you should be able to achieve what you want using RANKX along with some other measures.  Here is a great article that describes the steps in achieving hoepfully what you are after: https://www.burningsuit.co.uk/find-top-or-bottom-n-percent-dax/

Hope this helps!

Theo 

[ahuet]

Sorry for digging an old topic, but this is the first result that pops when searching this issue.

It seems that the behavior of RANKX has changed since then, because 

Customers Ranked Top = RANKX (Customers,[_Total Sales])

doesn't work anymore. It should be changed this way :

Customers Ranked Top = RANKX (ALL(Customers),[_Total Sales])

 See video from Benjamin Ejzenberg (in French)  Power bI DAX Challenges - 05 RANKX