如何在PowerBI中按阶梯单价计算金额？

阶梯定价(也称为分段定价或批量折扣定价)是一种常见的定价策略，即根据数量的不同区间应用不同的单价。本篇文章将介绍如何在PowerBI中实现这种复杂的阶梯单价金额计算。

问题描述

某工厂对员工完成的订单数量采取阶梯单价（类似个税），请自行建模计算各员工应得到的金额，并确保总计的汇总正确。具体问题如下图所示：

本案例的初始数据如下：

订单完成表：

员工编号	完成订单数
1	1506
2	801
3	1200
4	0
5	3248
6	502
7	1462

区间单价表：

区间	最小值	最大值	单价
1-800	1	800	0.1
801-1200	801	1200	0.2
1201-1600	1201	1600	0.3
1601-2000	1601	2000	0.4
>=2001	2001	9999999999	0.5

若需其它辅助表等，可自行创建并建模。

解题要点

方法一：

1、首先找出员工完成订单数所涉及到的各个区间，只要单价区间的最小值小于或等于完成订单数，那么这些区间就是涉及到的区间。

2、对涉及到的区间进行迭代求和，将各个区间所对应的数值与对应单价相乘，以得到金额。

方法二：

采用个税常用的算法，即先计算出各个区间对应的速算扣除数，然后直接定位完成订单数所处的最高区间，然后将完成订单数与单价相乘并减去速算扣除数即可。

速算扣除数的具体算法逻辑可参考下图：

解决方案

首先，本案例无需连接关系，具体的模型如下图所示：

---

方法一：

首先，创建如下度量值：

金额 = 
SUMX(
    '订单完成表',
    SUMX(
        '区间单价表',
        PRODUCTX(
            {
                MIN('区间单价表'[最大值],'订单完成表'[完成订单数])-'区间单价表'[最小值]+1,
                '订单完成表'[完成订单数]>='区间单价表'[最小值],
                '区间单价表'[单价]
            },
            [Value]
        )
    )
)

然后创建一个矩阵，并将订单完成表中的员工编号字段作为行标签，再将上面的度量值以及完成订单数字段一起放入矩阵的值字段即可，如下图所示：

---

方法二：

首先，在区间单价表中创建一个计算列，用于计算速算扣除数，如下图所示：

所用到的计算列表达式如下：

速算扣除数 = 
SUMX(
    '区间单价表',
    ('区间单价表'[最小值]<EARLIER('区间单价表'[最小值]))
        *(EARLIER('区间单价表'[单价])-'区间单价表'[单价])
            *('区间单价表'[最大值]-'区间单价表'[最小值]+1)
)

然后，创建如下度量值：

金额-速算版 = 
SUMX(
    VALUES('订单完成表'[员工编号]),
    VAR Num = CALCULATE(SUM('订单完成表'[完成订单数]))
    RETURN
    SUMX(
        FILTER('区间单价表','区间单价表'[最小值]<=Num && Num<='区间单价表'[最大值]),
        Num*'区间单价表'[单价]-'区间单价表'[速算扣除数]
    )+0
)

然后创建一个矩阵，并将订单完成表中的员工编号字段作为行标签，再将上面的度量值以及完成订单数字段一起放入矩阵的值字段即可，如下图所示：

总结

以上方法仅供参考，若有更优雅的解决方案，欢迎留言讨论~