如何在筛选器上下文中保留列并执行求和计算？

Question

我试图通过一个动态乘数来返回未来4年的总和，用于不同的财务预测，但我似乎无法让它发挥作用。想知道有没有人能帮上忙？

我的数据在一个扁平的桌子上：

ForecastName    FiscalYear  TransactionType Value
Forecast1      2019         borrowing       10
Forecast1      2020         borrowing       10
Forecast1      2021         borrowing       10
Forecast1      2022         borrowing       20
Forecast1      2023         borrowing       25
Forecast2      2019         borrowing       30
Forecast2      2019         turnover        40
Forecast3      2019         turnover        50
Forecast3      2020         turnover        50

我有一个计算借款的方法：

Borrowing = CALCULATE(SUM(Table[Value]),Table[TransactionType]="borrowing")

以及对乘法器的度量(基本上它返回{1，1，0.5，0})：

Multiplier = 
VAR YearNumber = MAX(Table[Fiscal Year])-2018
RETURN
IF(2.5 > YearNumber,1, IF(2.5 > YearNumber-1,.5,0))

下面是我尝试编写sum产品风格度量的尝试：

Borrowing Forecast = 
VAR CurrentFiscalYear = MAX(Table[Fiscal Year])

RETURN
SUMX((FILTER(ALLEXCEPT(Table,Table[ForecastName]),
           Table[Fiscal Year] < CurrentFiscalYear + 4 &&
           Table[Fiscal Year] >= CurrentFiscalYear)
Mutiplier x Borrowing)

其思想是，当您将度量值放在行中带有FiscalYear的表上，并由ForecastName过滤时，它从行中获取财政年度并将其放入变量中，然后使用它在当前和下一个财政年度中过滤整个表，但在ForecastName上保留筛选器，然后执行计算并对其进行求和。该表显示了我想要的Forecast1结果：

Fiscal Year Borrowing Forecast - DESIRED
2019         25 ((10*1)+(10*1)+(10*0.5)+(20*0))
2020         30 ((10*1)+(10*1)+(20*0.5)+(25*0))

实际结果是一个很大的数字，我不明白，但我认为问题之一是，ALLEXCEPT部分似乎不工作。

我还想知道，我试图在SUMX的计算部分中使用的度量是否包含计算？

非常感谢！

Answer 1

这很棘手。如果您愿意在迭代器中定义乘数，那就不算太糟了，但我仍然在努力弄清楚如何在正确传递两行上下文的同时将其抽象出来。

以下是作为一种衡量标准的方法：

Borrowing Forecast = 
VAR ThisYear = SELECTEDVALUE ( 'Table'[FiscalYear] ) 
VAR Years =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[FiscalYear],
        "Multiplier", SWITCH (
            TRUE (),
            'Table'[FiscalYear] < ThisYear, 0,
            'Table'[FiscalYear] - ThisYear < 2, 1,
            'Table'[FiscalYear] - ThisYear < 3, 0.5,
            0
        ),
        "Borrowing", CALCULATE (
            SUM ( 'Table'[Value] ),
            'Table'[TransactionType] = "borrowing"
        )
    )
RETURN
    SUMX ( Years, [Multiplier] * [Borrowing] )

Borrowing测度易于单独定义，但乘数比较复杂。

好的。我想我已经得到了一些DAX黑客魔法。

为了计算乘数，让我们这样定义它

MinYear = CALCULATE ( MIN ( 'Table'[FiscalYear] ), ALL ( 'Table' ) )

Multiplier = 
VAR YearDiff = SELECTEDVALUE ( 'Table'[FiscalYear] ) - [MinYear]
RETURN
    SWITCH (
        TRUE (),
        YearDiff < 0, 0,
        YearDiff < 2, 1,
        YearDiff < 3, 0.5,
        0
    )

现在，我们可以将预测指标写成如下：

Forecast = 
VAR Offset = SELECTEDVALUE ( 'Table'[FiscalYear] ) - [MinYear]
RETURN
    SUMX (
        ALL ( 'Table'[FiscalYear] ),
        CALCULATE (
            [Multiplier],
            'Table'[FiscalYear] = EARLIER ( 'Table'[FiscalYear] ) - Offset
        ) * [Borrowing]
    )

注意，我们实际上是将EARLIER ( 'Table'[FiscalYear] ) - Offset作为变量传递给Multiplier度量。还请注意，Offset包含[MinYear]，它在传递后与YearDiff中的一个取消，以便有效地

YearDiff = EARLIER ( 'Table'[FiscalYear] ) - SELECTEDVALUE ( 'Table'[FiscalYear] )

前者是从行上下文的年份，后者是过滤器上下文的年份。

[MinYear]和Offset之所以存在，是因为您不能传递任意变量，而只能传递表中存在的变量，因此需要将事情转移到现有的年份内。

下面是矩阵中的样子：

​

​