有人知道在DAX中,Poisson分布公式是否有M等效的版本吗?我有一个查询,在其中我想要基于两列计算该公式,但无法在任何地方找到解决方案。或者我应该将查询添加到数据模型中,并在那里使用DAX来计算它?
有人知道在DAX中,Poisson分布公式是否有M等效的版本吗?我有一个查询,在其中我想要基于两列计算该公式,但无法在任何地方找到解决方案。或者我应该将查询添加到数据模型中,并在那里使用DAX来计算它?
List.Accumulate
实现累加和的示例:PoissonDist(k,mean,cdf)
。(k as number, mean as number, cdf as logical) as number =>
let
k = Number.RoundDown(k, 0),
Poisson = Number.Exp(-mean) * Number.Power(mean, k) / Number.Factorial(k),
Cumulative = List.Accumulate(List.Range({0..k},0), 0, (state, current) => state + Number.Exp(-mean) * Number.Power(mean, current) / Number.Factorial(current)),
Switch = if cdf then Cumulative else Poisson,
Result = if k < 0 or mean < 0 then error "Input cannot be negative." else Switch
in
Result
这个版本更加紧凑,但本质上是相同的东西:
(k as number, m as number, cdf as logical) as number =>
let
k = if k > 0 and m > 0 then Number.RoundDown(k,0) else error "Input cannot be < 0.",
Poisson = List.Accumulate(List.Range({0..k}, if cdf then 0 else k), 0,
(sum, i) => sum + Number.Exp(-m) * Number.Power(m, i) / Number.Factorial(i))
in
Poisson
亚历克西斯的答案在我的情况下有一些缺陷:
Poisson(0, 0, false)
在两个版本中都会因为Number.Power(0, 0)
而抛出错误。相比之下,Excel返回正确值1
(请参阅维基百科上的递归定义)。Number.Exp(-m)
可以移至列表操作之外(第二个版本)。所以这是我修改后的泊松分布版本:
(x as number, mean as number, cumulative as logical) as number =>
let
k = if x >= 0 then Number.RoundDown(x, 0) else error "x must not be negative",
m = if mean >= 0 then mean else error "mean must not be negative",
result = Number.Exp(-m) * (if k = 0 then 1 else List.Accumulate({1..k}, [sum = 1, element = 1], (state, i) => let next = state[element]*m/i in [sum = if cumulative then state[sum] + next else next, element = next])[sum])
in
result