Get max value of a row. exclude top 10 %

[Anonymous]

Hi,

I'm trying to think of how to dynamically get the max value of the remaining 90% if I exclude the top 10 % higest values of a column.

I've solved getting the average for the remaining by counting number of rows minus 90 % number of rows (rounddown), then dividing sum of my column with the with the previous calculation.

hope that was understandable.

Wondering if anyone has any ideas on how to solve this?

Would be great to show it as a kpi.

Thanks!

[bhanu_gautam · Accepted solution]

@Anonymous , You can try that using DAX and creating a measure

Max Remaining 90% =
VAR TotalRows = COUNTROWS('YourTable')
VAR RowsToExclude = ROUNDUP(TotalRows * 0.1, 0)
VAR RemainingRows = TotalRows - RowsToExclude
VAR RemainingMax = CALCULATE(MAX('YourTable'[YourColumn]), 'YourTable'[YourColumn] <= PERCENTILE.INC('YourTable'[YourColumn], 0.9))
RETURN RemainingMax