Average based on standard deviation threshold

[Anonymous]

Hello all.

I am working on a dashboard to evaluate transportation rates and provide the end user with an average cost based on selections made.

Selections are basically from and to and a few others.

What I want to do is evaluate the records based on selections and provide an average. That's easy enough. But I want to evaluate the records first and throw out any records with a cost that is outside the standard deviation. I'm trying to make the average as accurate as possible and want to avoid outliers increasing or decreasing the average artificially.

The data has a field called [Actual Cost] that I need to evaluate for the average. There are also records with 0 that I would want to exclude as well.

Seems I would need an IF statement in here. Just not sure on how the syntax would work.

These are the fields in the data load I'm working with.

     [FO ID],

     [FOI Origin],

     [FU ID],

     [SO Number],

     [DV Number],

     [PO Number],

     [Bulk/Pack],

     [Bulk/Pack Desc],

     Division,

     [Division Desc],

     [Actual Departure Date],

     [FO Stg Srce Loc],

     [From Name],

     [From City],

     [From State],

     [From Zip],

     [From City State],

     [FO Stg Dest Loc],

     [To Name],

     [To City],

     [To State],

     [To Zip],

     [To City State],

     [Executing Carrier],

     [Material Group],

     Material,

     [Material Desc],

     [Actual Cost],

     [Est Cost],

     Miles,

     [Stage Miles],

     [Gross Wgt],

     [Item Gr Wgt],

     [Stem Mi]

Any help is appreciated.

Thanks,

Nate

[Anil_Babu_Samineni]

Perhaps this?

IF Statement

If([Actual Cost] <> '0' or Is NotNull([Actual Cost]),Avg([Actual Cost]))

Set Analysis

Avg({<[Actual Cost] = {'>0'}>} [Actual Cost])

OR

Avg({<[Actual Cost] = {"=Avg([Actual Cost])>0"}>} [Actual Cost])