Calculate average duration without duration =< 1 second

[Applicable88]

Hello, 

what is the best way to calculate average duration but disregard durations smaller or equal 1 second?

My measure for all duration is now like this:

Measure= CALCULATE(AVERAGE('Machine'[Duration]))

Thank you very much in advance.

Best. 

[vivran22 · Accepted solution]

@Applicable88  Yes, you can.

AVERAGEX(FILTER('Machine', 'Machine'[Duration] > 1), 'Machine'[Duration])

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)


https://www.vivran.in/

Connect on LinkedIn

[mahoneypat · Accepted solution]

If your column was type Duration in the query editor, it was converted to a decimal value (in days) when loaded.  You can use a measure expression like this to filter out durations <1 sec.

NewMeasure = CALCULATION(SUM(Table[Duration]), Table[Duration] > 1/(24*60*60))

For more about modelling and "measure"-ing time/durations, please see these videos.

(4) It's About Time - Part One - YouTube

(4) It's About Time - Part Two - YouTube

Pat

[Applicable88]

@mahoneypat , thanks for the "duration type" solution. 

[Applicable88]

Hi @vivran22 thank you very much.

Is there also a way to write it with Averagex, in case I want to iterate.

Best. 

[vivran22 · Accepted solution]

@Applicable88  Yes, you can.

AVERAGEX(FILTER('Machine', 'Machine'[Duration] > 1), 'Machine'[Duration])

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)


https://www.vivran.in/

Connect on LinkedIn

[vivran22]

Hello @Applicable88 

You may try add a filter to your CALCULATE statement:

CALCULATE(AVERAGE('Machine'[Duration]), 'Machine'[Duration] > 1)

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂 (Hit the thumbs up button!)
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)


https://www.vivran.in/

Connect on LinkedIn