如何在Power BI中计算随时间变化阈值的甲乙双方分层佣金

我们有销售记录数据，分润规则如下：

• 仅当销售涉及乙方且营收超过10000美元时，甲方与乙方按对应阈值规则分润；未达阈值时甲方全额获取，乙方分润为0
• 分润阈值规则可由管理者随时更新，规则按生效日期生效（示例包含2025-01-01的规则变更）

阈值规则表 #

预期分润结果 #

1. 数据模型准备 #

确保销售记录表（命名为Sales）和阈值规则表（命名为SplitRules）已导入Power BI，无需建立表间关系，将通过DAX动态匹配规则。

2. 创建「Party A Split $」计算列 #

在Sales表中新建计算列，粘贴以下DAX代码：

Party A Split $ = 
VAR CurrentSaleDate = Sales[销售日期]
VAR CurrentRevenue = Sales[营收]
VAR IsInvolvePartyB = Sales[是否涉及乙方]
-- 获取当前销售日期适用的最新规则生效日期
VAR LatestRuleDate = CALCULATE(MAX(SplitRules[生效日期]), SplitRules[生效日期] <= CurrentSaleDate)
-- 匹配当前营收对应的最高阈值的甲方分润比例
VAR ApplicableAPercent = 
    CALCULATE(
        MAX(SplitRules[甲方分润比例]),
        SplitRules[生效日期] = LatestRuleDate,
        SplitRules[营收阈值] <= CurrentRevenue
    )
RETURN
    IF(
        IsInvolvePartyB = "是" && CurrentRevenue >= 10000,
        FORMAT(CurrentRevenue * ApplicableAPercent, "#,0") & " (" & FORMAT(ApplicableAPercent, "0%") & ")",
        IF(IsInvolvePartyB = "是", FORMAT(CurrentRevenue, "#,0") & "（未达阈值，无需分润）", FORMAT(CurrentRevenue, "#,0"))
    )

3. 创建「Party B Split $」计算列 #

同样在Sales表中新建计算列，粘贴以下DAX代码：

Party B Split $ = 
VAR CurrentSaleDate = Sales[销售日期]
VAR CurrentRevenue = Sales[营收]
VAR IsInvolvePartyB = Sales[是否涉及乙方]
-- 获取当前销售日期适用的最新规则生效日期
VAR LatestRuleDate = CALCULATE(MAX(SplitRules[生效日期]), SplitRules[生效日期] <= CurrentSaleDate)
-- 匹配当前营收对应的最高阈值的乙方分润比例
VAR ApplicableBPercent = 
    CALCULATE(
        MAX(SplitRules[乙方分润比例]),
        SplitRules[生效日期] = LatestRuleDate,
        SplitRules[营收阈值] <= CurrentRevenue
    )
RETURN
    IF(
        IsInvolvePartyB = "是" && CurrentRevenue >= 10000,
        FORMAT(CurrentRevenue * ApplicableBPercent, "#,0") & " (" & FORMAT(ApplicableBPercent, "0%") & ")",
        "0"
    )

关键逻辑说明 #

• LatestRuleDate：自动筛选销售日期前生效的最新规则版本，确保规则更新后无需手动调整
• ApplicableAPercent/ApplicableBPercent：在最新规则集中，找到小于等于当前营收的最高阈值对应的分润比例，实现区间匹配（比如2025年营收40000会匹配30000阈值的规则）
• 文本格式化：同时显示分润金额和比例，未达阈值时自动显示对应说明

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