Creating new table to pull a Min and Max value from while excluding outliers

[mschutt]

Hello,

I am currently working on a dashboard that shows all employees, their job title, their region, and their service rate. On my dashboard I have a tile that shows Min service rate as well as Max service rate. I need to exclude outliers from the min and max amounts. The criteria to remove outliers is as follows-

- Remove 2 highest and 2 lowest service rates from job title A / Region NA

- Remove highest (one) and lowest (one) service rates from job title B / Region ASIA

- Lastly remove anything left that is <= 10

Is it possible to do all of this in one table for my min and max values to pull off of? 

Sample Data Below:

[v-zhangti · Accepted solution]

Hi @mschutt ,

You can try the following methods.

Measure:

Remove two =
VAR _highest =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            [Rate] >= SELECTEDVALUE ( 'Table'[Rate] )
                && [Title] = "A"
                && [Region] = "NA"
        )
    )
VAR _lowest =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            [Rate] <= SELECTEDVALUE ( 'Table'[Rate] )
                && [Title] = "A"
                && [Region] = "NA"
        )
    )
RETURN
    IF (
        _highest <= 2,
        BLANK (),
        IF ( _lowest <= 2, BLANK (), SELECTEDVALUE ( 'Table'[Rate] ) )
    )

Remove one =
VAR _highest =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            [Rate] >= SELECTEDVALUE ( 'Table'[Rate] )
                && [Title] = "B"
                && [Region] = "ASIA"
        )
    )
VAR _lowest =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            [Rate] <= SELECTEDVALUE ( 'Table'[Rate] )
                && [Title] = "B"
                && [Region] = "ASIA"
        )
    )
RETURN
    IF (
        _highest <= 1,
        BLANK (),
        IF ( _lowest <= 1, BLANK (), SELECTEDVALUE ( 'Table'[Rate] ) )
    )

Remove Less than 10 =
IF (
    SELECTEDVALUE ( 'Table'[Rate] ) <= 10,
    BLANK (),
    SELECTEDVALUE ( 'Table'[Rate] )
)

Table 2:

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[ID],
    'Table'[Name],
    'Table'[Title],
    'Table'[Region],
    "Rate",
        IF (
            SELECTEDVALUE ( 'Table'[Rate] ) = [Remove two],
            BLANK (),
            IF (
                SELECTEDVALUE ( 'Table'[Rate] ) = [Remove one],
                BLANK (),
                IF (
                    SELECTEDVALUE ( 'Table'[Rate] ) = [Remove Less than 10],
                    SELECTEDVALUE ( 'Table'[Rate] )
                )
            )
        )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[AlexisOlson]

For each of these outlier conditions, you can find the list of corresponding ID numbers. Combine these lists and filter them out of the table.

In M, you could do this as follows:

let
    Source = <your data source goes here>,
    Sorted = Table.Sort(Source, {{"Rate", Order.Descending}}),
    Top2_A_NA   = Table.FirstN(Table.SelectRows(Source, each [Title] = "A" and [Region] = "NA" ), 2)[#"ID #"],
    Bot2_A_NA   = Table.LastN( Table.SelectRows(Source, each [Title] = "A" and [Region] = "NA" ), 2)[#"ID #"],
    Top1_B_ASIA = Table.FirstN(Table.SelectRows(Source, each [Title] = "B" and [Region] = "ASIA" ), 1)[#"ID #"],
    Bot1_B_ASIA = Table.LastN( Table.SelectRows(Source, each [Title] = "B" and [Region] = "ASIA" ), 1)[#"ID #"],
    Under_10    = Table.SelectRows(Source, each [Rate] < 10 )[#"ID #"],
    Combined = List.Combine({Top2_A_NA, Bot2_A_NA, Top1_B_ASIA, Bot1_B_ASIA, Under_10}),
    Filtered = Table.SelectRows(Source, each not List.Contains(Combined, [#"ID #"]))
in
    Filtered

You could use similar logic in DAX as well if you prefer.