Exclude greatest 5% of values from the data set (Avg. number of days) using a button click.

[Anonymous]

Exclude greatest 5% of values from the data set (Avg. number of days) using a button click.
Example: 100 line items in the grid. Button is clicked to exclude the 5% greatest average no. of days(Cycle time) line items. I have created a DAX measure for this but not able to fix this. Below is my DAX expression. I have around 27,000 records in my dataset. It is not at all able to exclude top 5% of NSRs.

Top 5% =

VAR _Avg =

    AVERAGE('Global_NSR'[NSR Approval Cycle Time(Actual) ])

VAR _top =

    CALCULATE (

        COUNTROWS ( ADDCOLUMNS ( VALUES ( Table[nsrnum] ), "_Avg", _Avg ) ),

        REMOVEFILTERS ( Table[nsrnum] )

    ) * 0.05   

    RETURN

   CALCULATE (

        CALCULATE ( AVERAGE('Table'[NSR Approval Cycle Time(Actual) ]) ),

        KEEPFILTERS (

            TOPN (

                _top,

                ALLSELECTED ( Table[nsrnum] ),

                CALCULATE ( AVERAGE('Table'[NSR Approval Cycle Time(Actual) ]) ), DESC

            )))
Seeing the same result after adding the Top 1% result. Top 5% measure is not at all loading.

TIA,

Tanisha

[mh2587 · Accepted solution]

1. Calculate the Threshold for the Top 5% Values
Create a measure to determine the value that separates the top 5% of NSR Approval Cycle Time (Actual):
Top_5_Percent_Threshold = 
VAR TotalCount = COUNTROWS('Global_NSR')
VAR Top5PercentCount = INT(TotalCount * 0.05)
RETURN
CALCULATE (
    MAXX (
        TOPN (
            Top5PercentCount,
            'Global_NSR',
            'Global_NSR'[NSR Approval Cycle Time(Actual)],
            DESC
        ),
        'Global_NSR'[NSR Approval Cycle Time(Actual)]
    )
)

2. Create a Calculated Table Excluding the Top 5% Values
Create a new calculated table that excludes the top 5% of values based on the calculated threshold:
Filtered_Global_NSR = 
VAR Threshold = [Top_5_Percent_Threshold]
RETURN
FILTER (
    'Global_NSR',
    'Global_NSR'[NSR Approval Cycle Time(Actual)] <= Threshold
)


By creating this calculated table, you can then use it in your reports and visualizations to work with the dataset excluding the top 5% of NSR Approval Cycle Time (Actual) values.