如何在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屋！