Power BI中按职位去重并按年份展示福利费率的方案咨询

我正在从总福利与收入总额维度分析公司各职位成本，计算员工福利比率，已按年份确定各职位的福利费率并完成透视，但职位编号仍存在重复。期望得到如下格式的结果：

部分年份因职位空缺存在空值，请问该用Power Query还是DAX实现？我已用Distinct创建职位编号表，尝试用LookupValue函数：

2018 = LookupValue('PositionNumberBenefitRateByYear'[2018],PositionNumberBenefitRateByYear[Employee Position.Position Number], 'Table'[Employee Position.Position Number])

但报错：“提供了多值表，但需要单个值。”

两种工具都能实现需求，分场景选择即可：

一、Power Query实现（推荐，适合数据预处理） #

1. 加载包含职位编号和各年份福利费率的数据源
2. 选中「Employee Position.Position Number」列，点击主页 > 删除重复项，直接去除重复职位编号
3. 若同一职位同一年份存在多个费率值，先按「职位编号+年份」分组，用平均值/最大值/最小值聚合费率（根据业务逻辑选择），再执行透视：
   • 选中「Employee Position.Position Number」列，点击转换 > 透视列，值列选福利费率，列值选年份，空值保留即可
4. 将处理完成的表加载到数据模型，直接得到目标格式结果

二、DAX实现（适合数据模型内动态计算） #

你用LOOKUPVALUE报错的核心原因是：同一职位编号在PositionNumberBenefitRateByYear表中对应了多个2018年费率值，该函数要求匹配结果唯一。可改用以下两种方法：

方法1：取最新的非空费率值 #

2018 = 
CALCULATE(
    LASTNONBLANK('PositionNumberBenefitRateByYear'[2018],1),
    FILTER(
        'PositionNumberBenefitRateByYear',
        'PositionNumberBenefitRateByYear'[Employee Position.Position Number] = 'Table'[Employee Position.Position Number]
    )
)

方法2：聚合多值（以平均值为例） #

如果同一职位同一年份有多个有效费率，可按业务规则聚合：

2018 = 
CALCULATE(
    AVERAGE('PositionNumberBenefitRateByYear'[2018]),
    FILTER(
        'PositionNumberBenefitRateByYear',
        'PositionNumberBenefitRateByYear'[Employee Position.Position Number] = 'Table'[Employee Position.Position Number]
    )
)

补充：基于去重职位表的计算 #

如果你已经通过DISTINCT生成了唯一职位编号表，可在该表中新建计算列，使用上述DAX公式关联费率表，即可得到每个职位对应的各年份唯一费率值。

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