Calculate a Change in Values - Omitting Errors

[liamcameron]

I have Engine hours being recorded every day across many engines across a fleet of vehicles. Occasionally the hours are put in incorrectly. I'm attempting to create a table that will show the average usage of an engine over a month. Currently I have a measure that takes max(hours) - min(hours) and works UNLESS there is an errant number in the set. 

i.e: below, on the 1st of january the value was 16. So clearly, my max-min would not work. Nor would simply saying "value at end of month - value at beginning of month"... though how to do that is another question!

Range[Asset]Range[Date]Range[Component]Range[Hours]

Allaveurte	2019-01-31	Port Main Engine	11435
Allaveurte	2019-01-30	Port Main Engine	11427
Allaveurte	2019-01-29	Port Main Engine	11419
Allaveurte	2019-01-28	Port Main Engine	11416
Allaveurte	2019-01-27	Port Main Engine	11412
Allaveurte	2019-01-26	Port Main Engine	11410
Allaveurte	2019-01-25	Port Main Engine	11408
Allaveurte	2019-01-24	Port Main Engine	11404
Allaveurte	2019-01-23	Port Main Engine	11380
Allaveurte	2019-01-22	Port Main Engine	11356
Allaveurte	2019-01-21	Port Main Engine	11340
Allaveurte	2019-01-20	Port Main Engine	11340
Allaveurte	2019-01-19	Port Main Engine	11322
Allaveurte	2019-01-18	Port Main Engine	11298
Allaveurte	2019-01-17	Port Main Engine	11274
Allaveurte	2019-01-16	Port Main Engine	11250
Allaveurte	2019-01-15	Port Main Engine	11237
Allaveurte	2019-01-14	Port Main Engine	11235
Allaveurte	2019-01-13	Port Main Engine	11232
Allaveurte	2019-01-12	Port Main Engine	11226
Allaveurte	2019-01-11	Port Main Engine	11226
Allaveurte	2019-01-10	Port Main Engine	11222
Allaveurte	2019-01-09	Port Main Engine	11211
Allaveurte	2019-01-08	Port Main Engine	11199
Allaveurte	2019-01-07	Port Main Engine	11188
Allaveurte	2019-01-06	Port Main Engine	11182
Allaveurte	2019-01-05	Port Main Engine	11158
Allaveurte	2019-01-04	Port Main Engine	11134
Allaveurte	2019-01-03	Port Main Engine	11125
Allaveurte	2019-01-01	Port Main Engine	16

[v-lionel-msft]

Hi @liamcameron ,

"I'm attempting to create a table that will show the average usage of an engine over a month. "

Do you want to exclude the wrong hours and then average it over a month?

As shown in the table below, 16 is an incorrect value, so you need to exclude it and then average it, right?

But there is a question here, based on what do we judge whether this value is wrong or correct?

And is this what you want?

average usage of an engine = 
AVERAGEX(
    FILTER(
        Range,
        [Hours] >= 10000 && [Hours] <= 12000
    ),
    [Hours]
) 

Best regards,
Lionel Chen

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

[liamcameron]

Hey Lionel. 

So yes, the 16 there is an error, and I want it omitted. 

Later, I have a measure that calculates Max[hours] - Min[hours]

[amitchandak]

Create a new column like given below

Column = if(Range[Hours]/AVERAGEX(Range,Range[Hours])>.85 && Range[Hours]/AVERAGEX(Range,Range[Hours]) <=1.15,1,0)

You can change the range of valid values depeand on need. Now take Min and max by excluding 0.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[liamcameron]

So to do a sales analogy... pretend that is my net revenue for a year, but what it was on day 1, day 2. So it's always increasing. the [hours] column is a total. i'm trying to find what was the average change, but excluding those errors