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 ...
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.

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.