Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
Awesome @amitchandak! Just curious: why do you use
DIVIDE(3, 2) = 1,5
instead of
3 / 2 = 1,5
Thanks for your help.
@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
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]))
Divide Performs division and returns alternate result or BLANK() on division by 0.
https://docs.microsoft.com/EN-US/dax/divide-function-dax
Proud to be a Super User!
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.
Proud to be a Super User!
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?
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.
Proud to be a Super User!
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]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |