使用DAX测量,根据多个条件计算客户的重复联系次数。

huangapple go评论66阅读模式
英文:

Calculate with DAX measurements the number of repeated contacts from customers based on multiple conditions

问题

我正在开发一个Power BI报告,在此报告中,根据数据,我应该能够计算呼入电话的重复联系率。数据是按日级别提供的。对于重复联系,我应该考虑客户在同一天多次拨打电话(这是对于成功的呼叫),如果客户的呼叫被放弃(不成功,未连接到操作员),则不仅考虑同一天,而且要考虑直到首次成功呼叫(随后的天数)。每日结果应按周和月级别进行汇总。
结果应根据报告上应用的筛选器而发生变化,可以是国家、队列、时间范围。

我已经创建了一个在日级别上运行的测量:

Unique =
CALCULATE (
    DISTINCTCOUNT ( 'Repeated Contact'[Customer] ),
    ALLEXCEPT (
        'Repeated Contact',
        'Repeated Contact'[Start date],
        'Repeated Contact'[Division Group],
        'Repeated Contact'[Division],
        'Repeated Contact'[Queue_Dep],
        'Repeated Contact'[Queue_Type],
        'Repeated Contact'[Name]
    )
)

但是当我在周或月上进行汇总时,我得到的联系较少,如果我手动将每日结果相加,可能是因为聚合结果不是每日结果的相加,而是在周或月时间范围内进行计算。
这仅适用于同一天的重复联系。我尚未找到与放弃联系有关的解决方案。

英文:

I am working on a Power BI report, where based on the data, I should be able to calculare the Repeated contact rate for inbound calls. The data is on daily level. For the Repeated contact I should consider if the customer call on the same day more than 1x (this is for successfull calls), and if the customer call was abandon (not successfull, not reach the opertor) than not only the same day is considered but until the first successfull call (next days). The daily result shoud be agregated to weekly and monthly level.
The result should change accordingly what filter is applied on the report, it could be, country, queue, timeframe.

I created a measurement what works on daily level:

Unique =
CALCULATE (
    DISTINCTCOUNT ( 'Repeated Contact'[Customer] ),
    ALLEXCEPT (
        'Repeated Contact',
        'Repeated Contact'[Start date],
        'Repeated Contact'[Division Group],
        'Repeated Contact'[Division],
        'Repeated Contact'[Queue_Dep],
        'Repeated Contact'[Queue_Type],
        'Repeated Contact'[Name]
    )
)

but when I agregate on week or month i got less contact that if I add up manually the days, probably as agregated result is not add up the daily result, but do the calculation on the week or month timeframe.
This is only for the same day repeated contacts. I did not managed to came up with something for the abandoned contacs.

答案1

得分: 0

如果您想要为多天汇总此度量值,您将需要使用如下公式:

Unique (Agg) = 
SUMX ( 
   VALUES ( 'Calendar'[Date] ) , 
   [Unique]
)

这假定您已经设置了一个与您的数据连接的日历表,这是进行时间智能计算的最佳实践。

如果没有专门的日历表,您可以尝试使用当前评估上下文中您的表的可用日期的这个度量值:

Unique (Agg) = 
SUMX ( 
   VALUES ( 'Repeated Contact'[Start date] ) , 
   [Unique]
)
英文:

If you want to aggregate this measure for several days, you will need a formula like this:

Unique (Agg) = 
SUMX ( 
   VALUES ( 'Calendar'[Date] ) , 
   [Unique]
)

This assumes that you have a calendar table set up that is connected to your data, as is best practice for any time intelligence calculations.

Without a dedicated calendar table you can try this measure that uses the available dates of your table in the current evaluation context (whatever that may be):

Unique (Agg) = 
SUMX ( 
   VALUES ( 'Repeated Contact'[Start date] ) , 
   [Unique]
)

huangapple
  • 本文由 发表于 2023年2月8日 21:42:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/75386674.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定