PowerBI PVM 模型 - 数学推导与 DAX 实现的完美展示

BI佐罗

发布于 2021-11-04 14:28:04

1.2K1

发布于 2021-11-04 14:28:04

在 Power BI 中实现 PVM 模型，是一个将理论用于模型的良好示范，它展示精确的数据理论计算以及在 Power BI 数据模型的加持下，如何实现大规模计算。在 2021 年 3 月的文章，我们写了本文的第一篇，并留了思考题：DAX 如何实现以及 Mix 到底代表了什么，本文给出全部答案。

价格销量混合模型（Price-Volume-Mix），是分析企业营收差异变化原因的高级分析模型，其标准实现以及扩展变化可能形成多种实际形态。

模型的可视化

考虑某年（AC）相对去年（PY）的增长的构成如下：

由于价格提升，带来的增长。
由于销量提升，带来的增长。
由于新品，带来的增长。
由于下架，带来的损失。

模型的扩展

如果使用小多图，将整套业务拆到更细粒度，则有：

可以看出，新品的力量是显著的。

当然也可以看看不同销售人员的表现，如下：

可以看出，销售经理们的业绩改变也主要依赖新老产品的交替，能够更好的把握新品的推广，显著促进增长；而对原有销售规模比较大的经理，由于暂时无法全面替换，导致新品对比老品下架的增长是有限的。

我们当然在 Power BI 中有详细的计算，如下：

问题来了，我们如何实现在 Power BI 数据模型中的大规模准确计算呢？

基本思想

从微观的角度，对于某种产品，如：《BI 真经》有其定价，该定价与市场的客户心理预期有一种隐含的关系，他们通常表现为：

如果价格普遍高于心理预期，销量下降；
如果价格普遍低于心理预期，销量上升。

如何找到产品服务与客户心理预期的最佳匹配点是实现理论最大营收的关键所在。

同一产品的价格可能会得到调整，表现如下：

如果合适的降价而带来大幅销量提升导致整体业绩提升，记作：A；
如果合适的涨价而销量并未显著降低导致整体业绩提升，记作：B。

那么，

如果 A > B，则降低价格提升销量的策略更好；
如果 A < B，则上调价格业绩提升的策略更好；
同时，价格调整与销量变化的平衡点可以在这个过程中拿捏。

这个思想其实是业务逻辑常识，虽然简单，却可以将营收的差异分解为：价格变化，销量变化的结构化。

微观恒等式

对于一笔交易（Transaction），也就是在订单（Order）中的行，有：

本订单内某产品服务带来的毛利（Gross Margin）= 本订单内该产品服务的折后单价（Price）* 本订单内该产品服务的被购买数量（Volume）

产品和服务的本质都是产品，下面统称为产品。

尽管如此，但同一产品可能存在不同的打折方式，而导致在不同的交易中，产品表现出来的价格不同。

于是有：

在一段时间范围，某产品的平均折后售价 = 该产品的总毛利 / 该产品的总销量

Price-Volume-Mix 模型

首先需要对模型进行简化，假设所有的产品 SKU 在两年内都有销售，不存在新品和下架的情况，对重要的三个量给出推导过程，如下：

DAX 实现

根据上述思想以及严格的数学理论推导，在 DAX 中实现如下：

PVM.Volume.AC = 
SUM( 'Order'[数量] )

PVM.Volume.PY = 
CALCULATE( [PVM.Volume.AC] , DATEADD( Clendar[日期] , -1 , YEAR ) )

PVM.Revenue.AC = 
SUM( 'Order'[LineSellout] )

PVM.Revenue.PY = 
CALCULATE( [PVM.Revenue.AC] , DATEADD( 'Clendar'[日期] , -1 , YEAR ) )

PVM.Price.AC = 
[PVM.Revenue.AC] / [PVM.Volume.AC]

PVM.Price.PY = 
[PVM.Revenue.PY] / [PVM.Volume.PY]

// 以下给出三大重要计算，理论依据已经如上严格证明。

// 由于价格上涨，带来的增长：

PVM.Revenue.Δ.Price = 
SUMX( 
    VALUES( 'Product'[产品ID] ) , 
    IF( 
        [PVM.Price.AC] * [PVM.Price.PY] <> 0 , 
        ( [PVM.Price.AC] - [PVM.Price.PY] ) * [PVM.Volume.PY] 
    ) 
)

// 由于销量上升，带来的增长：

PVM.Revenue.Δ.Volume = 
SUMX( 
    VALUES( 'Product'[产品ID] ) , 
    IF( 
        [PVM.Price.AC] * [PVM.Price.PY] <> 0 , 
        ( [PVM.Volume.AC] - [PVM.Volume.PY] ) * [PVM.Price.PY] 
    ) 
)

// 由于价格上涨和销量上升，其混合效应带来的增长：

PVM.Revenue.Δ.Mix = 
SUMX( 
    VALUES( 'Product'[产品ID] ) , 
    IF( 
        [PVM.Price.AC] * [PVM.Price.PY] <> 0 , 
        ( [PVM.Volume.AC] - [PVM.Volume.PY] ) * ( [PVM.Price.AC] - [PVM.Price.PY] ) 
    ) 
)

实现完毕。