如何在PowerBI中计算商品的动态单价及折扣？

在零售和电商数据分析中，动态计算商品单价及折扣是一项常见的需求。本篇文章将介绍如何在PowerBI中实现这一功能，包括数据模型设计、DAX表达式编写等。

问题描述

请自行建模计算销售的总金额与折扣价金额，计算规则如下：

1、订单表里只有数量，价格为动态价格，将根据日期动态调价，其中商品价格定义为最接近订单日期的最后一次调整的价格。

2、商品所属的大类与小类有不定期的折扣活动，检查某个订单的下单时间是否具有折扣，对于同一个大类或小类，若其有多个折扣，则取最近一个活动的折扣

3、如果大类小类均有折扣，以折扣力度大的为准，若无折扣，则不打折

4、关于折扣的计算，直接乘以对应折扣即可，比如折扣为62%，那么折扣价金额为：总金额×62%

具体问题如下图所示：

本案例的初始数据如下：

大类：

大类
儿童
家具

小类：

小类
玩具
文具
办公家具
普通家具

大类折扣：

大类	折扣开始日期	折扣结束日期	折扣
儿童	2021年1月11日	2021年1月17日	0.62
家具	2021年3月4日	2021年3月10日	0.95
儿童	2021年5月23日	2021年5月24日	0.7
家具	2021年7月4日	2021年7月9日	0.84

小类折扣：

小类	折扣开始日期	折扣结束日期	折扣
办公家具	2021年1月18日	2021年1月28日	0.74
文具	2021年4月2日	2021年4月7日	0.72
普通家具	2021年4月21日	2021年4月27日	0.65
办公家具	2021年4月22日	2021年4月29日	0.9
文具	2021年4月29日	2021年5月2日	0.66
玩具	2021年5月19日	2021年5月25日	0.62
普通家具	2021年5月21日	2021年5月29日	0.92
文具	2021年5月24日	2021年5月25日	0.91
办公家具	2021年6月3日	2021年6月6日	0.74
普通家具	2021年8月19日	2021年8月26日	0.89
办公家具	2021年9月13日	2021年9月16日	0.89

商品类别：

大类	小类	商品
儿童	玩具	乐高
儿童	玩具	遥控车
儿童	玩具	小飞机
儿童	文具	笔
儿童	文具	本
家具	办公家具	办公桌
家具	办公家具	椅子
家具	普通家具	柜子
家具	普通家具	床
家具	普通家具	沙发

调价表：

商品	价格	生效日期
乐高	1000	2021年1月1日
遥控车	500	2021年1月1日
小飞机	300	2021年1月1日
笔	5	2021年1月1日
本	5	2021年1月1日
办公桌	1000	2021年1月1日
椅子	2000	2021年1月1日
柜子	3000	2021年1月1日
床	5000	2021年1月1日
沙发	8000	2021年1月1日
沙发	8480	2021年1月29日
笔	5.75	2021年1月31日
笔	5.25	2021年3月5日
遥控车	480	2021年4月2日
床	5250	2021年5月28日
沙发	7200	2021年6月23日
本	5.55	2021年7月15日
办公桌	880	2021年7月23日
小飞机	270	2021年8月22日
本	5.8	2021年9月3日

销售订单：

日期	商品	数量
2021年1月7日	乐高	47
2021年1月7日	椅子	23
2021年1月12日	办公桌	36
2021年1月22日	本	28
2021年2月7日	柜子	37
2021年2月11日	床	13
2021年2月12日	小飞机	27
2021年2月18日	小飞机	28
2021年3月10日	办公桌	37
2021年3月20日	沙发	28
2021年3月28日	办公桌	27
2021年3月29日	床	28
2021年3月30日	乐高	31
2021年4月2日	办公桌	18
2021年4月2日	柜子	25
2021年4月3日	小飞机	28
2021年4月14日	遥控车	32
2021年4月19日	办公桌	12
2021年4月24日	柜子	8
2021年4月26日	办公桌	49
2021年4月29日	办公桌	42
2021年5月2日	沙发	31
2021年5月6日	本	5
2021年5月10日	柜子	18
2021年5月11日	床	17
2021年5月13日	沙发	32
2021年5月14日	遥控车	4
2021年5月16日	椅子	44
2021年5月19日	床	28
2021年5月23日	沙发	28
2021年5月26日	本	10
2021年6月7日	本	20
2021年6月7日	笔	36
2021年6月15日	乐高	30
2021年6月16日	椅子	10
2021年6月18日	小飞机	43
2021年6月26日	笔	46
2021年6月26日	笔	5
2021年6月28日	沙发	35
2021年6月30日	笔	38
2021年7月1日	乐高	1
2021年7月2日	小飞机	25
2021年7月5日	床	24
2021年7月6日	本	24
2021年7月7日	办公桌	12
2021年7月15日	笔	22
2021年7月18日	沙发	21
2021年7月20日	椅子	39
2021年7月22日	小飞机	44
2021年7月27日	小飞机	12
2021年8月5日	沙发	17
2021年8月7日	椅子	40
2021年8月8日	本	20
2021年8月12日	笔	28
2021年8月19日	办公桌	46
2021年8月21日	遥控车	22
2021年8月22日	笔	6
2021年8月28日	柜子	25
2021年8月28日	办公桌	18
2021年8月30日	柜子	50
2021年9月5日	乐高	47
2021年9月6日	床	39
2021年9月7日	沙发	48
2021年9月12日	椅子	18
2021年9月18日	沙发	45

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

解题要点

本案例的计算逻辑应该还是比较直观的，但由于表的数量较多且模型设计的自由度较高，导致没有固定的套路或计算逻辑，因此这里给出一些可能有帮助的提示：

1、在设计模型时，可以结合扩展表原理的应用，尽量在计算时将商品及其所属的大类或小类的筛选传递到各个表，这样计算折扣或单价时只需要再添加一个日期的筛选即可。

2、获取折扣或单价时，可以先筛选出过去的数据，然后使用TOPN函数根据日期降序来获取最近的数据。

3、可以利用COALESCE函数或MIN函数来减少一些表达式的书写。

解决方案

首先设计数据模型，具体如下图所示：

然后，创建如下度量值：

总金额 = 
SUMX(
    '销售订单',
    CALCULATE(
        MAXX(TOPN(1,'调价表','调价表'[生效日期]),'调价表'[价格]),
        '调价表'[生效日期]<=EARLIER('销售订单'[日期])
    )*'销售订单'[数量]
)

折扣价金额 = 
SUMX(
    '销售订单',
    VAR CurDate = '销售订单'[日期]
    VAR Price = 
        CALCULATE(
            MAXX(TOPN(1,'调价表','调价表'[生效日期]),'调价表'[价格]),
            '调价表'[生效日期]<=CurDate
        )
    VAR Category_rebate = 
        CALCULATE(
            MAXX(TOPN(1,'大类折扣','大类折扣'[折扣开始日期]),'大类折扣'[折扣]),
            '大类折扣'[折扣开始日期]<=CurDate && CurDate<='大类折扣'[折扣结束日期]
        )
    VAR SubCategory_rebate = 
        CALCULATE(
            MAXX(TOPN(1,'小类折扣','小类折扣'[折扣开始日期]),'小类折扣'[折扣]),
            '小类折扣'[折扣开始日期]<=CurDate && CurDate<='小类折扣'[折扣结束日期]
        )
    VAR Finally_rebate = MIN(COALESCE(Category_rebate,1),COALESCE(SubCategory_rebate,1))
    RETURN
    '销售订单'[数量]*Price*Finally_rebate
)

然后创建一个矩阵，并将大类和子类作为行标签，再将上面的度量值放入矩阵的值字段即可，结果如下图所示：

总结

以上方法仅供参考，若有更优雅的解决方案，欢迎留言讨论，或者加入我们的技术交流群，一起享受这种思维碰撞的快乐吧！

PBI/DAX技术交流群(QQ)：344353627