Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create Calculated column for Outliers by group

Good morning,
I am new to PowerBI and I am trying to create a column that identifies outliers in a set of data.
I can do that if the data is not grouped in category with the following CalculatedColumn:

Outlier =
VAR QUARANTILE1 = PERCENTILE.INC(Table[Time],0.2)
VAR QUARANTILE4 = PERCENTILE.INC(Table[Time],0.8)
VAR INTERQUANRANTILERANGE = QUARANTILE4- QUARANTILE1
VAR MYUPPERBOUND = QUARANTILE4 + 1.2 * INTERQUANRANTILERANGE
RETURN Table[Time]>= MYUPPERBOUND

Where Table is the name of the Table/Query and Time is a Column (Whole number)

But I cannot do it by grouping the data according to another column.
Below is the syntax for my CalculatedColumn:

Outlier1 =
VAR QUARANTILE1 = SUMMARIZE(Table,Table[Type],"p", PERCENTILE.INC(Table[Time],0.2))
VAR QUARANTILE4 = SUMMARIZE(Table,Table[Type],"p", PERCENTILE.INC(Table[Time],0.8))
VAR INTERQUANRANTILERANGE = QUARANTILE4 - QUARANTILE1
VAR MYUPPERBOUND = QUARANTILE4 + 1.2 * INTERQUANRANTILERANGE
RETURN Table[Time]>= MYUPPERBOUND


Where Table is the name of the Table/Query, Type and Time are columns in the table

The error I get is " The expression refers to multiple columns.
Multiple columns cannot be converted to a scalar value."
I somehow understand what it is saying but I don't know how to achieve my goal.

Can someone help me to create this calculated column?

 

I am using Microsoft PowerBI Desktop (x64) Version 2.92.706.0

Need Help
  • daxer-almighty's avatar
    daxer-almighty
    5 years ago
    [Outlier by Type?] =
    var CurrentType = Table[Type]
    var CurrentTime = Table[Time]
    VAR Quartile1 =
        PERCENTILEX.INC(
            FILTER(
                'Table',
                Table[Type] = CurrentType
            ),
            'Table'[Time],
            0.2
        )
    VAR Quartile4 =
        PERCENTILEX.INC(
            FILTER(
                'Table',
                Table[Type] = CurrentType
            ),
            'Table'[Time],
            0.8
        )
    VAR IqrRange = Quartile4 - Quartile1
    VAR UpperBound = Quartile4 + 1.2 * IqrRange
    RETURN
        CurrentTime >= UpperBound

3 Replies

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    5 years ago
    [Outlier by Type?] =
    var CurrentType = Table[Type]
    var CurrentTime = Table[Time]
    VAR Quartile1 =
        PERCENTILEX.INC(
            FILTER(
                'Table',
                Table[Type] = CurrentType
            ),
            'Table'[Time],
            0.2
        )
    VAR Quartile4 =
        PERCENTILEX.INC(
            FILTER(
                'Table',
                Table[Type] = CurrentType
            ),
            'Table'[Time],
            0.8
        )
    VAR IqrRange = Quartile4 - Quartile1
    VAR UpperBound = Quartile4 + 1.2 * IqrRange
    RETURN
        CurrentTime >= UpperBound
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you! I worked perfectly.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , here

    Outlier1 =
    VAR QUARANTILE1 = SUMMARIZE(Table,Table[Type],"p", PERCENTILE.INC(Table[Time],0.2))
    VAR QUARANTILE4 = SUMMARIZE(Table,Table[Type],"p", PERCENTILE.INC(Table[Time],0.8))

    VAR INTERQUANRANTILERANGE = QUARANTILE4 - QUARANTILE1

     

    this like TableA -TableB , what you actually wanted to achieve here?

     

     

     

    Also, check this

    http://datacornering.com/how-to-identify-outliers-in-microsoft-power-bi/