解决Power BI中动态计算股票平均成本的循环依赖问题

场景与问题 #

我有一个包含数万条股票交易记录的数据集，字段包括ticker（股票代码）、dates（交易日期）、shares（交易股数）、types（买卖类型）、share prices（交易股价）。已实现以下度量值：

• Purchase total：买入股数 × 股价
• Total revenue：卖出股数 × 股价
• Total cost：卖出股数 × Average cost
• Shares balance：逐行统计每笔交易后的持仓余额

现在需要计算Average cost（平均成本），但陷入循环依赖错误，计算逻辑为：

• 卖出交易：直接沿用该股票此前的平均成本
• 买入交易：(对应ticker截至当前日期的所有买入总额 + 对应ticker当前日期之前的所有负向卖出总成本) / 当前日期的持仓余额

循环依赖的核心原因是：Total cost依赖Average cost，而Average cost又需要调用历史的Total cost，形成闭环。

解决方案（无计算列） #

核心思路是绕开对Total cost度量值的依赖，直接迭代历史交易计算累计持仓成本，避免循环引用。以下是可直接使用的DAX代码：

Average cost = 
VAR CurrentTicker = SELECTEDVALUE('Transactions'[ticker])
VAR CurrentDate = SELECTEDVALUE('Transactions'[dates])
VAR CurrentType = SELECTEDVALUE('Transactions'[types])
VAR CurrentShares = [Shares balance]

-- 计算当前交易前的累计持仓成本（买入加、卖出减）
VAR PriorCumulativeCost = 
    SUMX(
        FILTER(
            ALL('Transactions'),
            'Transactions'[ticker] = CurrentTicker &&
            'Transactions'[dates] < CurrentDate
        ),
        VAR TxnType = 'Transactions'[types]
        VAR TxnShares = 'Transactions'[shares]
        VAR TxnPrice = 'Transactions'[share prices]
        -- 获取该笔历史交易发生时的平均成本
        VAR PriorAvgCost = 
            CALCULATE(
                [Average cost],
                FILTER(
                    ALL('Transactions'),
                    'Transactions'[ticker] = CurrentTicker &&
                    'Transactions'[dates] < EARLIER('Transactions'[dates])
                )
            )
        RETURN
            IF(
                TxnType = "买入",
                TxnShares * TxnPrice,
                -TxnShares * PriorAvgCost
            )
    )

RETURN
SWITCH(
    CurrentType,
    "卖出",
        -- 卖出时取最近一次历史平均成本
        CALCULATE(
            [Average cost],
            FILTER(
                ALL('Transactions'),
                'Transactions'[ticker] = CurrentTicker &&
                'Transactions'[dates] < CurrentDate
            ),
            LASTDATE('Transactions'[dates])
        ),
    "买入",
        -- 买入时计算新加权平均成本
        DIVIDE(
            PriorCumulativeCost + ('Transactions'[shares] * 'Transactions'[share prices]),
            CurrentShares,
            0
        ),
    0 -- 非买卖交易返回0
)

关键说明 #

1. 避免循环的核心：所有对Average cost的引用都严格限定在当前交易日期之前的历史数据，不会触发对当前行Average cost的计算请求。
2. 第一笔交易处理：如果是某股票的第一笔买入交易，PriorCumulativeCost为空白，DIVIDE函数会自动将其视为0，最终计算结果为买入价除以持仓股数，符合逻辑。
3. 性能优化：通过FILTER(ALL('Transactions'), ...)精准筛选同股票的历史交易，配合SUMX迭代计算，在数万行数据量下仍能保持可接受的性能。

内容的提问来源于stack exchange，提问作者leolapa