如何在PowerBI中对同一列的先前单元格进行求和 [英] How to perform sum of previous cells of same column in PowerBI
问题描述
我正在尝试将 Excel 公式复制到 PowerBI.这是
I am trying to replicate Excel formula to PowerBI.Which is
是否有任何 DAX 来执行此计算((1-0.2)*B2+0.2*C2).谢谢.
Is There any DAX to perform this calculation((1-0.2)*B2+0.2*C2). Thanks.
推荐答案
在 DAX 中没有进行相对行引用的固有方法,因此您需要明确告诉它要引用哪一行.
There no inherent way to do relative row reference in DAX, so you need to explicitly tell it which row to reference.
CalculatedColumn =
VAR PrevDate =
MAXX (
FILTER ( Table1, Table1[Date] < EARLIER ( Table1[Date] ) ),
Table1[Date]
)
VAR B = LOOKUPVALUE ( Table1[B], Table1[Date], PrevDate )
VAR C = LOOKUPVALUE ( Table1[C], Table1[Date], PrevDate )
RETURN
( 1 - 0.2 ) * B + 0.2 * C
<小时>
既然您已经澄清您希望引用您定义的同一列,那么我知道如何做到这一点的唯一方法是创建一个封闭式公式来使用,如 我的回答在这里.
Since you've clarified that you're looking to reference the same column that you are defining, the only way I know how to do this is to create a closed-form formula to use, as in my answer here.
与递推关系
C_(n+1) = 0.8 * B_n + 0.2 * C_n
我们可以根据 C_1
将其重写如下:
we can rewrite this in terms of C_1
as follows:
C_n = 0.8 * ( sum_(i=1)^(n-1) ( B_i * 0.2^(n-i-1) ) ) + 0.2^(n-1) * C_1
这里,整个C
列只依赖于B
列和一个初始值C_1 = 8320
,即第一项在 B
列中.
Here, the entire C
column is only dependent on column B
and a single initial value C_1 = 8320
, which is the first term in the B
column.
现在我们可以用一点 DAX Magic 把它变成一个计算列:
Now we can turn this into a calculated column with a little DAX Magic:
ColumnC =
VAR C1 = MAXX ( TOPN ( 1, TableN, TableN[Date], ASC ), [B] )
VAR N = RANK.EQ ( [Date], TableN[Date], ASC )
VAR SumTable =
ADDCOLUMNS (
FILTER (
SELECTCOLUMNS (
TableN,
"i", RANK.EQ ( [Date], TableN[Date], ASC ),
"B_i", [B]
),
[i] <= N - 1
),
"B_i Term", POWER ( 0.2, N - [i] - 1 ) * [B_i]
)
RETURN
IF (
N > 1,
0.8 * SUMX ( SumTable, [B_i Term] ) + POWER ( 0.2, N - 1 ) * C1,
0
)
这篇关于如何在PowerBI中对同一列的先前单元格进行求和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!