Average excluding outliers

[volfiedParticipant Started: 8Replies: 14Total: 22]

I’m having trouble with an average calculation that tries to exclude outliers. Basically, something like TRIMMEAN.

Here are the measures I’ve created:

AVG:=AVERAGE(Events[quest_answer])
StdDev:=STDEV.P(Events[quest_answer])
UpperB:=AVERAGE(Events[quest_answer])+2*STDEV.P(Events[quest_answer])
LowerB:=AVERAGE(Events[quest_answer])-2*STDEV.P(Events[quest_answer])
GPA:=CALCULATE(AVERAGE(Events[quest_answer]),FILTER(Events,Events[quest_answer]>[LowerB] && Events[quest_answer]<[UpperB]))

If I sub in hard-coded values for LowerB and UpperB in the GPA measure, it works, so the problem is something about nesting these measures. I’m having trouble coming up with a workaround, though. Any suggestions would be greatly appreciated.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hi Volfied,

Nested measures inside of a FILTER inside of a CALCULATE can be tough to work through.

With DAX, sometimes it is “what you do not see is what you get” and in other cases “what you do not have has a lot to do with what you did not get”.

For example, when you “hard code” replace the measures in GPA, for each iteration you have the same inputs row-by-row through your fact table, whereas with measures, they are re-evaluated row by row within the filter context, so for each iteration it is conceivable (probable) that the measures may never have the same value as when all iterations are complete.

To bear out this point, I created a data set and started with your exact measures, plus an alternative for GPA that just replaced the nested measures with specific values returned by the stand alone component measures (AVG, StdDev, UpperB, LowerB). The two versions of GPA did not match until I wrapped each component measure in a CALCULATE using the original formula in position 1, and an ALL ( Events ) in position 2.

Something to think about. This may be a solution that works for you, or it may not.

Tom

[Author]

Posts