How to exclude top 5% of values from the dataset using DAX

[tanisha_10]

Hi,
How to exclude top 5% of values from the dataset using DAX? 
Example: 100 line items in the grid. A Button is clicked to exclude the 5% greatest average of days(Cycle Time) line items from the dataset?

TIA,

Tanisha

[v-xingshen-msft]

Hi @tanisha_10 ,

Thank you @Ritaf1983 very much for the solution, , to help you understand the problem, I also tried some other ways to solve the problem:

Measure 2 = VAR rowstoexclude=ROUNDUP(COUNTROWS('Table (2)')*0.05,0)
VAR _rank = 
   ADDCOLUMNS('Table (2)',"Rank",RANKX(ALL('Table (2)'),[averagedays],,DESC,Dense))
   RETURN SUMX(FILTER(_rank,[Rank]>rowstoexclude),[averagedays])

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[tanisha_10]

Hi @v-xingshen-msft ,I have created this measure, But dont see any diffenece in the NSR Number count. I just have to exclude the extra NSRs (Top 5%) who have greatest Avg Days values in the dataset. By a button click these Top 5% NSRs should be excluded.  Created below 2 Dax measures are not working as expected.

DAX 1: 

ExcludeTop5Percent =
VAR rowstoexclude =
ROUNDUP ( COUNTROWS ( 'ExcludeTbl' ) * 0.05, 0 )
VAR _rank =
ADDCOLUMNS (
'ExcludeTbl',
"Rank",
RANKX (
ALL ( 'ExcludeTbl' ),
'ExcludeTbl'[NSR Approval Cycle Time(Actual)],
,
DESC,
DENSE
)
)
RETURN
AVERAGEX (
FILTER ( _rank, [Rank] > rowstoexclude ),
'ExcludeTbl'[NSR Approval Cycle Time(Actual)]
)

DAX 2:

Top 5% =
VAR _Avg =
AVERAGE('ExcludeTbl'[NSR Approval Cycle Time(Actual) ])
VAR _top =
CALCULATE (
COUNTROWS ( ADDCOLUMNS ( VALUES ( ExcludeTbl[nsrnum] ), "_Avg", _Avg ) ),
REMOVEFILTERS ( ExcludeTbl[nsrnum] )
) * 0.05

RETURN

CALCULATE (
CALCULATE ( AVERAGE('ExcludeTbl'[NSR Approval Cycle Time(Actual) ]) ),
KEEPFILTERS (
TOPN (
_top,
ALLSELECTED ( ExcludeTbl[nsrnum] ),
CALCULATE ( AVERAGE('ExcludeTbl'[NSR Approval Cycle Time(Actual) ]) ), DESC
)
)
)

TIA,

Tanisha

[v-xingshen-msft]

Hi @tanisha_10 ,

I'm sorry for not getting back to you until now, and I apologise for that.
If the reason it doesn't work is that it's not possible to use in measure
ROUNDUP ( COUNTROWS ( 'ExcludeTbl' ) * 0.05, 0 ) he calculates it step by step and summarises it at the end, so it will be wrong, we can use this formula to calculate the columns will solve this problem perfectly.

Rank = RANKX('CycleTimeData','CycleTimeData'[CycleTime],,DESC,Dense)

Column = ROUNDUP(COUNTROWS('CycleTimeData')*0.05,0)

Measure 2 = COALESCE(
  SUMX(FILTER(
    'CycleTimeData',
    'CycleTimeData'[Rank]>'CycleTimeData'[Column]),
    [CycleTime]),
    0
    )

Once again, I apologise for not resolving your issue in a timely manner.

Hope it helps!

Best regards,

Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.