Average time calculation, calculated column or measure?

Question

Hi all,&nbsp;I'm trying to get my head around PowerBI and measures and I couldn't find an answer, please help.&nbsp;This is an excerpt from my datatable of 18th of June 2020 during different time slots:&nbsp;NumCallsWaitingTimeAvg800:00:181000:00:20500:00:08500:00:05400:00:04600:00:331000:00:051700:00:31400:00:12&nbsp;I'm trying to calculate the average number of seconds waiting time on that day. Question:1. Is it best practice to add a Calculated Column or a Measure?2. What would the Measure look like?&nbsp;Note on question 2:&nbsp;If I use this:&nbsp;MEASURE = SUM(Table[WaitingTimeAvg]*86400)&nbsp;I don't get the right answer since it should be doing (NumCalls *&nbsp;WaitingTimeAvg) / NumCalls. But if I use this:&nbsp;MEASURE = (SUM(Table[NumCalls]) * SUM(Table[WaitingTimeAvg]*86400)) / SUM(Table[NumCalls])&nbsp;&nbsp;it doesn't work either... I would really like to understand what's going on.&nbsp;&nbsp;Can someone give me some clarity on my way to a PowerBI pro?Thanks!

ryan_mayu · Answer

BoPowerBI&nbsp;&nbsp;Maybe you can try create a column first.totalseconds = 'Table (2)'[numcalls]*'Table (2)'[wating]*24*3600&nbsp;Then create a measureavgseconds = sum('Table (2)'[totalseconds])/sum('Table (2)'[numcalls])Hope this is helpful.

ryan_mayu · Answer

BoPowerBI&nbsp;&nbsp;I searched some materials online. I didn't find sumproduct function in DAX. That will be better if we only use one measure to get the result.&nbsp;Let's see if anyone else can provide better solution on this.

bopowerbi · Answer

Thanks for pointing me toward sumproduct. I actually found it in this article.Syntax: = SUMX(&lt;Table&gt;, &lt;expression&gt;)Solution:&nbsp;&nbsp;WaitingTimeAvgSec = SUMX(WaitingTable,WaitingTable[NumCalls] * WaitingTable[WaitingTimeAvg] * 86400) / SUM(WaitingTable[NumCalls]&nbsp;&nbsp;

amitchandak · Answer

BoPowerBI ,
You have to try like
MEASURE = divide(SUMX(Table[NumCalls] *Table[WaitingTimeAvg]*86400),SUM(Table[NumCalls]))
&nbsp;
But prefer have avg time in sec or hour and use that
refer
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx

ryan_mayu · Answer

BoPowerBI&nbsp;Divide Performs division and returns alternate result or BLANK() on division by 0.&nbsp;https://docs.microsoft.com/EN-US/dax/divide-function-dax&nbsp;

Forum Discussion

Average time calculation, calculated column or measure?

9 Replies

Related content

DAX Calculated Measure

MoM calculation

Calculation Help

Calculate Tooltips

DAX Measure for Ranking the valid rows and calculating the average rating

NumCalls	WaitingTimeAvg
8	00:00:18
10	00:00:20
5	00:00:08
5	00:00:05
4	00:00:04
6	00:00:33
10	00:00:05
17	00:00:31
4	00:00:12