Forum Discussion
Impactful IndividualCalculate average duration without duration =< 1 second
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:
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 LinkedInIf 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
5 Replies
- vivran22
Community Champion
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 - mahoneypat
Microsoft Employee
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
Applicable88mahoneypat , 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
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