Forum Discussion

mschutt's avatar
mschutt
Regular Visitor
4 years ago
Solved

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

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:

 

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

2 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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.

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    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.