英文:
Calculate Average Service Duration PowerBI - multiple IDs
问题
我需要按照来源分组计算聊天机器人的平均服务时间。问题是，我只有每条消息的服务器日期。因此，我需要获取每个联系人的最大和最小日期时间，并计算差值（对于X是2分钟，对于Y是20分钟），然后得到全局平均持续时间（在这种情况下，对于来源A是11分钟，因为有2个唯一的联系人）。
有什么想法如何进行处理吗？
英文:
I need to calculate the average service time of a chatbot grouping by its source. The problem is I only have the date of the server to each message. Therefore, I need to take the max and min datetime of each contact and calculate the difference (2 mins for X and 20 mins for Y), and then obtain the global average duration (11 min for source A in this case since there are 2 unique contacts).
| Contact2__c | ServerDateTime__c | Source |
|---|---|---|
| X | 01/10/2022 13:20:00 | A |
| X | 01/10/2022 13:20:15 | A |
| X | 01/10/2022 13:22:00 | A |
| Y | 01/10/2022 14:00:00 | A |
| Y | 01/10/2022 14:05:00 | A |
| Y | 01/10/2022 14:20:00 | A |
Any idea on how to procceed ?
答案1
得分: 1
我认为我用了一种可怕的方式来做这个，但这就是我做的方式：
我使用SUMMARIZE创建了一个计算表，以返回每个联系人和来源的最早和最晚服务器时间，就像这样：
ContactSummary =
SUMMARIZE(
'ContactData',
'ContactData'[Contact2__c],
'ContactData'[Source],
"StartTime", MIN('ContactData'[ServerDateTime__c]),
"EndTime", MAX('ContactData'[ServerDateTime__c]),
"Duration",DATEDIFF(
MIN('ContactData'[ServerDateTime__c]),
MAX('ContactData'[ServerDateTime__c]),
MINUTE)
)
SUMMARIZE基本上让我按[Contact2__c]和[Source]进行分组，所以我从中得到了两个值（20和2）。
然后我创建了一个平均持续时间的度量：
avg duration = AVERAGE('ContactSummary'[Duration])
最后，我将来源和平均持续时间放在一个表中，所以我最终得到{ "A", 11.00}作为我的最终结果。A的总和为22，行数为2，所以22/2=11。
也许我解释得太多了，但如果我犯了一个错误，有人应该能够很容易地指出来。=)
英文:
I think I did this a hideous way, but this is how I did it:
I created a calculated table using SUMMARIZE to return the earliest and latest Server times per Contact and Source, like this:
ContactSummary =
SUMMARIZE(
'ContactData',
'ContactData'[Contact2__c],
'ContactData'[Source],
"StartTime", MIN('ContactData'[ServerDateTime__c]),
"EndTime", MAX('ContactData'[ServerDateTime__c]),
"Duration",DATEDIFF(
MIN('ContactData'[ServerDateTime__c]),
MAX('ContactData'[ServerDateTime__c]),
MINUTE)
)
SUMMARIZE basically lets me do a grouping by [Contact2__c] and [Source] so I get two values from that (20 and 2).
Then I created an Average Duration measure:
avg duration = AVERAGE('ContactSummary'[Duration])
Finally, I put Source and Avg Duration in a table, so I end up with {"A", 11.00} as my final result. Sum of A = 22, count of rows = 2, so 22/2 = 11.
Maybe I overexplained it, but if I made a mistake someone should be able to point it out pretty easily. =)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论