Forum Discussion

MidiGlitch's avatar
MidiGlitch
Frequent Visitor
9 years ago
Solved

Filter data by excluding max and min values

Hi All:

I have a table grouped by Ids and for each Id there are several rows. Unfortunately, both the max and min values of a column I wish to display are spurious and I want to filter them out of the aggregates.

 

i.e. something like the following psuedo code:

Average all values in col X that are not Min[X] nor Max[X] Group By ID

 any thoughts?

 

thx

Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    Hi MidiGlitch,

     

    Add this measure to your data:

    Average_Without_Max_MIN =
VAR Maximum =
    MAX ( Table2[Values] )
VAR Minimum =
    MIN ( Table2[Values] )
RETURN
    CALCULATE (
        AVERAGE ( Table2[Values] ),
        Table2[Values] <> Maximum
            && Table2[Values] <> Minimum
    )

    As you can see below it ignores the max and minimum in each group

     

     

    Regards,

    MFelix

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    9 years ago

    Hi MidiGlitch,

     

    Add this measure to your data:

    Average_Without_Max_MIN =
VAR Maximum =
    MAX ( Table2[Values] )
VAR Minimum =
    MIN ( Table2[Values] )
RETURN
    CALCULATE (
        AVERAGE ( Table2[Values] ),
        Table2[Values] <> Maximum
            && Table2[Values] <> Minimum
    )

    As you can see below it ignores the max and minimum in each group

     

     

    Regards,

    MFelix

    • MidiGlitch's avatar
      MidiGlitch
      Frequent Visitor
      9 years ago

      Excellent, except...

       

      After allowing DAX expressions in Options, I am just getting back zeros