Forum Discussion

Frequent Visitor

6 years ago

Average time calculation, calculated column or measure?

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

Icon for Super User rank

Super User

6 years ago

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
Frequent Visitor
6 years ago
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
  Super User
  6 years ago
  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
    Frequent Visitor
    6 years ago
    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]