Excel数据透视表与度量值：实现ID去重计数及关联金额去重求和

你的原始数据如下：

ID  Amount State Category
1   10     MyState MyCategory1
1   10     MyState MyCategory2
1   10     MyState MyCategory3
2   20     MyState MyCategory1
2   20     MyState MyCategory2
3   30     MyState MyCategory1

你的核心问题是：直接对Amount求和会重复计算同一ID的金额（比如ID=1的10会被算3次），但用DISTINCT COUNT统计的是去重后ID数量，两者统计逻辑不匹配，导致结果不一致。

以下是具体解决方法，通过Power Pivot创建自定义度量值实现：

1. 启用Power Pivot并进入数据模型 #

点击Excel顶部「数据」选项卡，选择「管理数据模型」，打开Power Pivot编辑器。

2. 创建去重金额求和的度量值 #

在Power Pivot编辑器的「主页」选项卡中点击「新建度量值」，输入以下DAX公式（记得把你的表名替换成你实际的表格名称）：

去重金额总和 = SUMX(DISTINCT('你的表名'[ID]), CALCULATE(MAX('你的表名'[Amount])))

公式说明 #

• DISTINCT('你的表名'[ID])：提取所有不重复的ID
• CALCULATE(MAX('你的表名'[Amount]))：对每个去重ID，取其对应的金额（因为同一ID的金额一致，用MAX/MIN/SUM都能得到正确单值）
• SUMX：遍历所有去重ID，将每个ID的金额累加，得到最终去重后的总金额

你也可以用另一等价公式，逻辑相同：

去重金额总和 = SUMX(GROUPBY('你的表名', '你的表名'[ID], "单ID金额", MAXX(CURRENTGROUP(), '你的表名'[Amount])), [单ID金额])

3. 在数据透视表中使用该度量值 #

回到Excel，刷新数据透视表，在字段列表里找到你创建的「去重金额总和」，将其拖入「值」区域替换原有的Amount求和项。此时该度量值的结果会和ID的DISTINCT COUNT完全匹配，每个ID仅被计算一次金额。

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