Filter data by excluding max and min values

[MidiGlitch]

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

[MFelix · Accepted solution]

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]

Excellent, except...

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

[MFelix]

Hi @MidiGlitch,

How is your visuals set-up? Did you do a Measure?

Regards,

MFelix