How to calculate the maximum value of a column without outliers?

[Anonymous]

Hi all,

I want to calculate the maximum value per ID without outliers, let's say minus 5%.

I already have a piece of a code, see below. But this code only returns one static value and not a value calculated by ID.

This is my situation:

I have one table, named Table1 with the following columns: ID, Speed (measure), Weight, Length.

I want to calculate the maximum Weight if Speed > 0 and Length > 0 and I want to cut the top 5% (outliers).

Table1:
ID Speed Weight Length
A 30 100 180
A 32 95 190
A 29 102 180
B 35 80 0
B 40 70 187
B 41 68 170
C 40 70 160
C 33 98 190
C 30 100 0

The output should be:

Table1:
ID Speed Weight Length MaxWeight
A 30 100 180 100
A 32 95 190 100
A 29 102 180 100
B 35 80 0 68
B 40 70 187 68
B 41 68 170 68
C 40 70 160 70
C 33 98 190 70
C 30 100 0 70

VAR UpperQuartile =
    PERCENTILEX.INC ( Table1; Table1[Weight]; ,95 )

RETURN
CALCULATE(IF(Table1[Speed] > 0 ;MAX(Table1[Weight]));FILTER(Table1;Table1[Length] > 0 && Table1[Weight] <= UpperQuartile))

[Icey · Accepted solution]

Hi @Anonymous ,

You can create your measure or column like so:

MaxWeight =
VAR UpperQuartile =
    CALCULATE (
        PERCENTILEX.INC ( Table1, Table1[Weight], .95 ),
        ALLEXCEPT ( Table1, Table1[ID] )
    )
RETURN
    IF (
        [Speed Measure] > 0,
        CALCULATE (
            MAX ( Table1[Weight] ),
            FILTER ( ALL ( Table1 ), Table1[Length] > 0 && Table1[Weight] <= UpperQuartile )
        )
    )

Best Regards,
Icey

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

[Anonymous]

This helps, thanks!!