如何在Power BI中创建用于价格弹性分析的计算表/Power Query表

前提准备 #

确保模型中表关系正确：订单表与订单项表通过订单ID建立一对多关系，订单表与日历表通过日期建立一对多关系。

方法一：DAX计算表 #

方案1：使用SUMMARIZECOLUMNS跨表聚合（推荐） #

SUMMARIZECOLUMNS支持直接调用多表字段，无需先合并表，代码更简洁：

价格弹性分析基础表 = 
VAR 每日产品聚合 =
    SUMMARIZECOLUMNS (
        '日历表'[日期],
        '订单项表'[产品ID],
        "当日销量", SUM('订单项表'[数量]),
        "当日均价", AVERAGE('订单项表'[单价])
    )
RETURN
    ADDCOLUMNS (
        每日产品聚合,
        "前一日销量", 
            CALCULATE (
                SUM('订单项表'[数量]),
                DATEADD('日历表'[日期], -1, DAY),
                ALLEXCEPT('订单项表', '订单项表'[产品ID])
            ),
        "前一日均价",
            CALCULATE (
                AVERAGE('订单项表'[单价]),
                DATEADD('日历表'[日期], -1, DAY),
                ALLEXCEPT('订单项表', '订单项表'[产品ID])
            )
    )

代码说明：

1. 先通过SUMMARIZECOLUMNS按「日历表日期+产品ID」聚合，计算当日销量和均价
2. 用ADDCOLUMNS结合CALCULATE+DATEADD，获取同产品前一日的销量和均价，ALLEXCEPT确保只保留当前产品的筛选上下文

方案2：基于SUMMARIZE的兼容写法 #

如果偏好SUMMARIZE，可先给订单项表关联订单日期，再聚合：

价格弹性分析基础表_SUMMARIZE = 
VAR 带日期的订单项 =
    ADDCOLUMNS (
        '订单项表',
        "订单日期", RELATED('订单表'[日期])
    )
VAR 每日产品聚合 =
    SUMMARIZE (
        带日期的订单项,
        [订单日期],
        [产品ID],
        "当日销量", SUM([数量]),
        "当日均价", AVERAGE([单价])
    )
RETURN
    ADDCOLUMNS (
        每日产品聚合,
        "前一日销量",
            CALCULATE (
                SUM('订单项表'[数量]),
                FILTER (ALL('日历表'), '日历表'[日期] = [订单日期] - 1),
                ALLEXCEPT('订单项表', '订单项表'[产品ID])
            ),
        "前一日均价",
            CALCULATE (
                AVERAGE('订单项表'[单价]),
                FILTER (ALL('日历表'), '日历表'[日期] = [订单日期] - 1),
                ALLEXCEPT('订单项表', '订单项表'[产品ID])
            )
    )

可选：补全缺失日期 #

如果需要包含无订单的日期（避免弹性计算断档），可生成日期-产品网格后左关联聚合数据：

价格弹性分析基础表_补全日期 = 
VAR 所有日期 = ALL('日历表'[日期])
VAR 所有产品 = ALL('订单项表'[产品ID])
VAR 日期产品网格 = CROSSJOIN(所有日期, 所有产品)
VAR 每日产品聚合 =
    SUMMARIZECOLUMNS (
        '日历表'[日期],
        '订单项表'[产品ID],
        "当日销量", SUM('订单项表'[数量]),
        "当日均价", AVERAGE('订单项表'[单价])
    )
VAR 完整聚合表 = NATURALLEFTOUTERJOIN(日期产品网格, 每日产品聚合)
RETURN
    ADDCOLUMNS (
        完整聚合表,
        "前一日销量", 
            CALCULATE (
                SUM('订单项表'[数量]),
                DATEADD('日历表'[日期], -1, DAY),
                ALLEXCEPT('订单项表', '订单项表'[产品ID])
            ),
        "前一日均价",
            CALCULATE (
                AVERAGE('订单项表'[单价]),
                DATEADD('日历表'[日期], -1, DAY),
                ALLEXCEPT('订单项表', '订单项表'[产品ID])
            )
    )

方法二：Power Query实现 #

通过数据合并、分组、排序、添加前序行数据完成，步骤直观：

let
    // 1. 合并订单项与订单表，提取订单日期
    合并订单表 = Table.NestedJoin(订单项表, {"订单ID"}, 订单表, {"订单ID"}, "订单表", JoinKind.LeftOuter),
    提取日期列 = Table.ExpandTableColumn(合并订单表, "订单表", {"日期"}, {"日期"}),
    // 2. 清理冗余列（可选）
    清理列 = Table.RemoveColumns(提取日期列, {"订单ID"}),
    // 3. 按日期+产品ID分组，计算当日销量和均价
    分组聚合 = Table.Group(清理列, {"日期", "产品ID"}, {
        {"当日销量", each List.Sum([数量]), type number},
        {"当日均价", each List.Average([单价]), type number}
    }),
    // 4. 按产品ID分组，处理单产品的日期序列
    按产品分组 = Table.Group(分组聚合, {"产品ID"}, {{"数据组", each _, type table [日期=date, 产品ID=text, 当日销量=number, 当日均价=number]}}),
    // 5. 对每个产品组排序并添加前一日数据
    添加前一日数据 = Table.TransformColumns(按产品分组, {{"数据组", each 
        let
            按日期排序 = Table.Sort(_, {{"日期", Order.Ascending}}),
            添加前一日销量 = Table.AddColumn(按日期排序, "前一日销量", each if Table.PositionOf(按日期排序, _) = 0 then null else 按日期排序[当日销量]{Table.PositionOf(按日期排序, _)-1}, type number),
            添加前一日均价 = Table.AddColumn(添加前一日销量, "前一日均价", each if Table.PositionOf(按日期排序, _) = 0 then null else 按日期排序[当日均价]{Table.PositionOf(按日期排序, _)-1}, type number)
        in
            添加前一日均价
    }}),
    // 6. 展开数据组并调整列顺序
    展开数据 = Table.ExpandTableColumn(添加前一日数据, "数据组", {"日期", "当日销量", "当日均价", "前一日销量", "前一日均价"}),
    调整列序 = Table.ReorderColumns(展开数据, {"日期", "产品ID", "当日销量", "当日均价", "前一日销量", "前一日均价"})
in
    调整列序

操作说明：

1. 先通过合并表给订单项补充订单日期
2. 按日期和产品分组计算核心指标
3. 按产品拆分后，在组内按日期排序，通过Table.PositionOf获取前一行的销量和均价
4. 最终展开所有组得到完整分析表

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