Power BI矩阵中基于缺失值的库存扣除与百分比计算需求

嘿，我来帮你搞定Power BI里这个带库存扣除的百分比计算问题！根据你的需求，咱们优先用DAX度量值来实现，因为它能完美适配Matrix的上下文筛选，比计算列更灵活。如果你的数据集缺少未销售员工的行，再用Power Query补全就行。

核心逻辑回顾 #

咱们的目标是：对每个「产品+国家」组合，先扣除未参与该产品在该国销售的员工的库存，再用实际销量/预测销量除以调整后的库存。比如你举的例子：加拿大City Bike总库存12044，扣除未销售的Mary的2321库存，用565/(12044-2321)计算比率。

第一步：用DAX创建「调整后库存」度量值 #

这个度量值会自动识别当前上下文的产品和国家，筛选出有销售记录的员工，再计算扣除未销售员工库存后的有效库存：

Adjusted Overall Inventory = 
VAR CurrentProduct = SELECTEDVALUE('你的表名'[Product])
VAR CurrentCountry = SELECTEDVALUE('你的表名'[Country])
-- 找出当前产品+国家下有销售记录的所有员工（只要有数据行就算，不管销量是否为0）
VAR SalespeopleWithSales = 
    CALCULATETABLE(
        VALUES('你的表名'[Salesperson]),
        ALL('你的表名'),
        '你的表名'[Product] = CurrentProduct,
        '你的表名'[Country] = CurrentCountry
    )
-- 计算当前产品+国家的总库存（所有员工的库存总和）
VAR TotalInventory = 
    CALCULATE(
        SUM('你的表名'[Overall Inventory]),
        ALL('你的表名'),
        '你的表名'[Product] = CurrentProduct,
        '你的表名'[Country] = CurrentCountry
    )
-- 计算未销售员工的库存总和
VAR ExcludedInventory = 
    CALCULATE(
        SUM('你的表名'[Overall Inventory]),
        ALL('你的表名'),
        '你的表名'[Product] = CurrentProduct,
        '你的表名'[Country] = CurrentCountry,
        NOT('你的表名'[Salesperson] IN SalespeopleWithSales)
    )
-- 返回调整后库存，处理ExcludedInventory为空白的情况（比如所有员工都有销售）
RETURN TotalInventory - COALESCE(ExcludedInventory, 0)

第二步：创建两个百分比比率度量值 #

基于上面的调整后库存，分别计算实际销量和预测销量的比率：

1. 实际销量比率 #

Sold Extension Pass Ratio = 
VAR AdjustedInv = [Adjusted Overall Inventory]
VAR SoldTotal = SUM('你的表名'[Sold Extension Pass])
-- 避免除以0，返回空白而不是错误
RETURN IF(AdjustedInv = 0, BLANK(), SoldTotal / AdjustedInv)

2. 预测销量比率 #

Forecast Extension Pass Ratio = 
VAR AdjustedInv = [Adjusted Overall Inventory]
VAR ForecastTotal = SUM('你的表名'[Forcast Sold Extension Pass])
RETURN IF(AdjustedInv = 0, BLANK(), ForecastTotal / AdjustedInv)

可选：如果数据集缺少未销售员工的行 #

如果你的数据集里，未销售的员工没有对应的「产品+国家」数据行（导致无法获取他们的库存），需要先在Power Query中补全所有可能的员工+产品+国家组合：

1. 打开Power Query编辑器，加载你的数据集。
2. 复制下面的代码（替换你的表名为实际表名），粘贴到高级编辑器中：

let
    Source = '你的表名',
    // 获取三个维度的唯一值列表
    Salespeople = List.Distinct(Source[Salesperson]),
    Products = List.Distinct(Source[Product]),
    Countries = List.Distinct(Source[Country]),
    // 创建员工+产品+国家的笛卡尔积表（所有可能组合）
    AllCombinations = Table.FromCrossJoin({Salespeople}, {Products}, {Countries}),
    // 重命名列
    RenamedColumns = Table.RenameColumns(AllCombinations,{{"Column1", "Salesperson"}, {"Column2", "Product"}, {"Column3", "Country"}}),
    // 左连接原表，获取库存、销量数据
    MergedQueries = Table.NestedJoin(RenamedColumns, {"Salesperson", "Product", "Country"}, Source, {"Salesperson", "Product", "Country"}, "SalesData", JoinKind.LeftOuter),
    // 展开合并的字段
    ExpandedSalesData = Table.ExpandTableColumn(MergedQueries, "SalesData", {"Overall Inventory", "Sold Extension Pass", "Forcast Sold Extension Pass"}, {"Overall Inventory", "Sold Extension Pass", "Forcast Sold Extension Pass"}),
    // 将空白的销量/预测值填充为0（可选，不影响DAX计算，但显示更友好）
    FilledSold = Table.ReplaceValue(ExpandedSalesData,null,0,Replacer.ReplaceValue,{"Sold Extension Pass"}),
    FilledForecast = Table.ReplaceValue(FilledSold,null,0,Replacer.ReplaceValue,{"Forcast Sold Extension Pass"})
in
    FilledForecast

3. 关闭并应用Power Query，此时你的数据集会包含所有员工+产品+国家的组合，未销售的行销量为0，库存保持原样。

使用方法 #

把这三个度量值添加到Matrix可视化中：

• 行/列可以放Product、Country、Salesperson任意组合
• 值区域添加Sold Extension Pass Ratio和Forecast Extension Pass Ratio
• 可以右键度量值，设置格式为百分比，调整小数位数

这样就能得到你需要的、自动扣除未销售员工库存的比率了！

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