Average time calculation, calculated column or measure?

[BoPowerBI]

Hi all,

I'm trying to get my head around PowerBI and measures and I couldn't find an answer, please help.

This is an excerpt from my datatable of 18th of June 2020 during different time slots:

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

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?

Note on question 2: 

If I use this:

MEASURE = SUM(Table[WaitingTimeAvg]*86400)

I don't get the right answer since it should be doing (NumCalls * WaitingTimeAvg) / NumCalls. But if I use this:

MEASURE = (SUM(Table[NumCalls]) * SUM(Table[WaitingTimeAvg]*86400)) / SUM(Table[NumCalls])

 it doesn't work either... I would really like to understand what's going on. 

Can someone give me some clarity on my way to a PowerBI pro?

Thanks!

[amitchandak]

@BoPowerBI ,

You have to try like

MEASURE = divide(SUMX(Table[NumCalls] *Table[WaitingTimeAvg]*86400),SUM(Table[NumCalls]))

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-i...

[BoPowerBI]

Awesome @amitchandak! Just curious: why do you use

DIVIDE(3, 2) = 1,5

instead of

3 / 2 = 1,5

Thanks for your help.

[amitchandak]

@BoPowerBI , Not sure I got you.

But Multiplication should be Sum(A*B) not sum(A) * sum(B) . If we want sum(A) * sum(B), we should force a row context

Similarly, divide should be sum(A)/ sum(B) not Sum(A/B) . A/B is done when you want a simple Avg

[BoPowerBI]

@amitchandak 

I just meant the difference between mine without divide:

WaitingTimeAvgSec = SUMX(WaitingTable,WaitingTable[NumCalls] * WaitingTable[WaitingTimeAvg] * 86400) / SUM(WaitingTable[NumCalls]

and yours with divide:

MEASURE = divide(SUMX(Table[NumCalls] *Table[WaitingTimeAvg]*86400),SUM(Table[NumCalls]))

[ryan_mayu]

@BoPowerBI 

Divide Performs division and returns alternate result or BLANK() on division by 0.

https://docs.microsoft.com/EN-US/dax/divide-function-dax

[ryan_mayu]

@BoPowerBI 

Maybe you can try create a column first.

totalseconds = 'Table (2)'[numcalls]*'Table (2)'[wating]*24*3600

 

Then create a measure

avgseconds = sum('Table (2)'[totalseconds])/sum('Table (2)'[numcalls])

Hope this is helpful.

[BoPowerBI]

Hi Ryan,

Thanks! I was specifically wondering (see my first question) if it is best practice to use a measure only or if I can use a solution like you propose. I've heard that Calculated Columns weigh too much on the speed of the data model or is it only the case in super complex calculated columns?

Any ideas?

[ryan_mayu]

@BoPowerBI 

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.

Let's see if anyone else can provide better solution on this.

[BoPowerBI]

Thanks for pointing me toward sumproduct. I actually found it in this article.

Syntax: = SUMX(<Table>, <expression>)

Solution:

WaitingTimeAvgSec = SUMX(WaitingTable,WaitingTable[NumCalls] * WaitingTable[WaitingTimeAvg] * 86400) / SUM(WaitingTable[NumCalls]