如何创建按 Agent 汇总的 Complex aggregation view

结论

先将 [Amount Credit] 和 [Amount Debit] 两个金额列转换为“交易类型 + 金额”的行结构，再用 Matrix（矩阵）视图汇总：

• Rows：Transaction type、payment type
• Columns：Agent
• Values：Sum of Amount
• Subtotals：按 Transaction type 显示小计

如果直接用当前的宽表创建普通交叉表，通常无法将 Credit 和 Debit 分成两个行区块，也很难分别生成每个 Agent 的小计。因此，需要先对金额列执行 Unpivot（取消透视）。

转换后的数据结构

原始 Table A 类似：

转换后应为：

改成长表后，就可以按多个行层级分组、按列透视，并显示分组小计。

使用 Power Query 转换

如果使用 Power BI 或支持 Power Query 的 Excel，可以在 Power Query 编辑器中完成转换：

1. 选中 [Amount Credit] 和 [Amount Debit]。
2. 选择 Transform → Unpivot Columns。
3. 将生成的 Attribute 列重命名为 Transaction type。
4. 将生成的 Value 列重命名为 Amount。
5. 确认 Amount 的数据类型为数值类型。

对应的 M 代码如下：

let
    Source = #"Table A",
    UnpivotedAmounts = Table.Unpivot(
        Source,
        {"Amount Credit", "Amount Debit"},
        "Transaction type",
        "Amount"
    ),
    ChangedType = Table.TransformColumnTypes(
        UnpivotedAmounts,
        {
            {"Transaction type", type text},
            {"Amount", type number}
        }
    )
in
    ChangedType

如果表名包含空格，Power Query 通常需要使用 #"" 形式引用，例如 #"Table A"。

配置 Matrix 视图

在矩阵可视化中按以下方式设置字段：

Rows
  Transaction type
  payment type

Columns
  Agent

Values
  Sum of Amount

格式设置如下：

• 开启 Row subtotals。
• 如果支持按层级配置小计，只为 Transaction type 层级开启小计。
• 根据展示需要，关闭 payment type 层级的小计。
• 如果希望使用接近平铺的布局，可以关闭 Stepped layout。
• 将小计标签设为 Total。能否修改该标签，取决于所用产品和版本。

最终结构如下：

Transaction type     Agent 1   Agent 2

Amount Credit
  Cash                    20        40
  Credit Card             20        20
  Total                   40        60

Amount Debit
  Cash                    20        40
  Credit Card             10        10
  Total                   30        50

不修改原表的 DAX 方案

如果不能通过 Power Query 调整数据结构，也可以创建计算表。以下写法适用于常见的 Power BI DAX 模型：

Agent Transactions =
UNION(
    SELECTCOLUMNS(
        'Table A',
        "payment type", 'Table A'[payment type],
        "Agent", 'Table A'[Agent],
        "Transaction type", "Amount Credit",
        "Amount", 'Table A'[Amount Credit]
    ),
    SELECTCOLUMNS(
        'Table A',
        "payment type", 'Table A'[payment type],
        "Agent", 'Table A'[Agent],
        "Transaction type", "Amount Debit",
        "Amount", 'Table A'[Amount Debit]
    )
)

然后创建金额度量值：

Total Amount =
SUM('Agent Transactions'[Amount])

矩阵字段配置不变：

Rows:     Transaction type → payment type
Columns:  Agent
Values:   [Total Amount]

注意事项

• 不建议分别创建大量 Credit 和 Debit 度量值，再手工为每个 Agent 拼接列。Agent 增加后，这种做法需要持续维护。
• 如果同一个 Agent 和 Payment type 有多条记录，矩阵中的 SUM(Amount) 会自动汇总。
• Amount 必须是数值类型，否则可能只能使用 Count，不能使用 Sum。
• 是否将空值显示为 0，取决于展示要求。在 Power BI 中可以使用：

Total Amount Display =
COALESCE([Total Amount], 0)

• 如果 Credit 和 Debit 在计算净额时需要使用不同符号，可以另外创建净额度量值，但不必为展示结果修改原始金额：

Net Amount =
CALCULATE(
    SUM('Agent Transactions'[Amount]),
    'Agent Transactions'[Transaction type] = "Amount Credit"
)
-
CALCULATE(
    SUM('Agent Transactions'[Amount]),
    'Agent Transactions'[Transaction type] = "Amount Debit"
)

重点是先将金额字段规范化为行，再让矩阵按照“交易类型 → 支付类型 → Agent”的层级进行汇总，而不是编写更复杂的聚合公式。

备注：内容仅供参考。