es 加权模式 excel加权算法

前言：

使用excel计算月末一次加权平均法下的出库金额，其实是件很简单的事情。但是，excel也有它的不足之处，不同表之间的计算效率不高，数据量大之后很容易卡顿。纯excel的用户，其实很难养成良好的数据管理习惯。各类数据，甚至是同类数据的不同部分，也经常被分割成一个个分散的数据孤岛。excel一般不能启发用户理解，什么样的表格是存数据，什么样的表格是看数据的——因为excel的自由度太高了，以至于excel的规则就是没有规则。缺少规则，就造成可复用性低，难以进行批量化操作。这就使得全面分析这些数据非常困难，以致于常被搁置。

power BI就有它自己的优势，非常容易整合与规范数据，且计算效率非常高。笔者自从接触power BI之后，就有意无意地尽量使用power BI来解决问题。一者，power BI确实是一个值得投入时间学习的工具。二者，学习这个工具不能光靠学习资料对自己大脑的输入，也需要自己练习才能真正掌握。每个可以用来作练习的机会都是宝贵的。如果能将这种练习与解决自己眼前的现实问题合二为一，虽然当下可能效率慢一些，困难一些，时常需要拿回家再花时间仔细琢磨，但久而久之，必将从中受益。

---

思路：

能不能把简单记录的进销存数据导入power BI进行自动的出库金额计算呢？

观察一张简单的计算月末一次加权平均成本的表格，会发现[平均单价]依赖于上月的[剩余金额]。而[剩余金额]又依赖于本月的[平均单价]。在excel上显示依赖关系的话，就显示成了如上图的这种类似麻花的形状。因为excel是单元格间引用的，所以这里并不会造成循环依赖。power BI使用DAX作为分析语言，DAX的计算是整列整列计算的。如果直接使用DAX模仿excel的计算方法的话，就会出现想要计算A列就需要先算出B列，要算B列又需要先算出A列，这种循环引用，先有鸡还是先有蛋的问题。当然，在导入数据的时候，使用PQ中的M语言是可以模仿excel的计算方法，这个以后再讲。

既然不能模仿excel的计算思路，那么只能另辟蹊径。月末一次加权平均算法，是在假设这月出库的物品里，上月结余的与本月入库的，它们的出库率（各自的出库数/各自的总数）是相同的。假设，上月结余了400件，本月入库了600件。这月仓库一共经手了1000件。然本月出库了700件，总的出库了70％。那么上月结余的400件是出掉了70％，280件；本月入库的600件也是出库了70％，420件。出库产品的金额也就是上月剩余金额的70％，加上本月入库金额的70％。剩余的金额是上月剩余金额的30％ + 本月入库金额的30%。

我们将剩余件数占仓库本月经手件数的比例，比如上例中的30%，称为保留率。则计算3月份剩余金额的公式可以表示为:
$\begin{aligned} &剩余金额_{3月}=剩余金额_{2月}\times保留率_{3月}+入库金额_{3月}\times保留率_{3月}\\ \\ &将(剩余金额_{2月})替换为(剩余金额_{1月}\times保留率_{2月}+入库金额_{2月}\times保留率_{2月})，则有：\\ \\ &剩余金额_{3月}=(剩余金额_{1月}\times保留率_{2月}+入库金额_{2月}\times保留率_{2月})\times保留率_{3月}+入库金额_{3月}\times保留率_{3月}\\ &=剩余金额_{1月}\times保留率_{2月}\times保留率_{3月}+入库金额_{2月}\times保留率_{2月}\times保留率_{3月}+入库金额_{3月}\times保留率_{3月}\\ \\ &再将(剩余金额_{1月})替换为(0\times保留率_{1月}+入库金额_{1月}\times保留率_{1月})，则有：\\ \\ &剩余金额_{3月}=入库金额_{1月}\times保留率_{1月}\times保留率_{2月}\times保留率_{3月}+入库金额_{2月}\times保留率_{2月}\times保留率_{3月}+入库金额_{3月}\times保留率_{3月}\\ \end{aligned}$
至此，我们很容易看出其中的计算规率了，第n月的剩余金额就是前n个月各月的入库金额被当月及以后各月的保留率反复累乘之后的结果的和。
$\begin{aligned} &\tag{1}\\ &剩余金额_{n月}=\sum_{i=1}^n (入库金额_{i月}\times\prod_{j=i}^n保留率_{j月}) \end{aligned}$

实践：

准备一份简单的出入库数据，全凭自己造。一般入库的时候是有金额的，而出库金额需要到月末，综合本月数据之后计算求得。

将数据导入power BI，在PQ中进行分组整合成各月的入库数量、入库金额、出库数量。并加载到数据模型。过程非常简单，文末已附原文件，不再赘述。

新增计算列，分别计算累计入库数量、累计出库数量、累计剩余数量、上期剩余+本期入库（即：本月的经手数量）、保留率。

累计入库数量 = //这是一个计算列
VAR vNowMonth='F01月末一次加权均法'[年月]
VAR vName='F01月末一次加权均法'[名称]
RETURN
CALCULATE(
    SUM('F01月末一次加权均法'[入库数量]),
    'F01月末一次加权均法'[年月]<=vNowMonth&&
    'F01月末一次加权均法'[名称]=vName,
    ALL('F01月末一次加权均法')
)

累计出库数量 = //这是一个计算列
VAR vNowMonth='F01月末一次加权均法'[年月]
VAR vName='F01月末一次加权均法'[名称]
RETURN
CALCULATE(
    SUM('F01月末一次加权均法'[出库数量]),
    'F01月末一次加权均法'[年月]<=vNowMonth&&
    'F01月末一次加权均法'[名称]=vName,
    ALL('F01月末一次加权均法')
)

累计剩余数量 = 'F01月末一次加权均法'[累计入库数量]-'F01月末一次加权均法'[累计出库数量]
//这是一个计算列

上期剩余+本期入库 = 'F01月末一次加权均法'[累计剩余数量]+'F01月末一次加权均法'[出库数量]
//这是一个计算列

保留率 = DIVIDE('F01月末一次加权均法'[累计剩余数量],'F01月末一次加权均法'[上期剩余+本期入库])
//这是一个计算列

接下来就到了本文的关键，使用DAX实现上面的公式(1)：

剩余金额 = //这是一个计算列
VAR vNowDate = 'F01月末一次加权均法'[年月]
VAR vName='F01月末一次加权均法'[名称]
RETURN
//使用SUMX实现累加∑，对单个产品首月至当前月的行进行计算后累加
SUMX(
    //使用Filter获取表格中，当前年月及以前的所有关于本产品的行(即首月至当前月)
    FILTER(
        ALL('F01月末一次加权均法'),
        'F01月末一次加权均法'[年月]<=vNowDate&&'F01月末一次加权均法'[名称]=vName
    ),
    VAR vNowDateInner= 'F01月末一次加权均法'[年月]//将首月至当前月中，正在被迭代到的那个月储存为变量vNowDateInner
    RETURN
    'F01月末一次加权均法'[入库金额]*  //各月入库金额
        PRODUCTX(  //使用PRODUCTX实现累乘 ∏,累乘的内容是，前面正在被迭代到的那个被储存为变量vNowDateInner的那个月，至当前月的所有保留率
            FILTER(
                'F01月末一次加权均法',
                'F01月末一次加权均法'[年月]>=vNowDateInner&&'F01月末一次加权均法'[年月]<=vNowDate&&'F01月末一次加权均法'[名称]=vName
            ),
            'F01月末一次加权均法'[保留率]
        )
)

可以看到计算结果与excel的计算结果一致：

另外，再提供一个度量值的版本，思路同上，不再赘述。

剩余金额.月末一次加权平均.月末.度量值 = 
VAR vNowDate = MAX('F01月末一次加权均法'[年月])
RETURN
CALCULATE(
	SUMX(
		'F01月末一次加权均法',
		VAR vNowDateInner= 'F01月末一次加权均法'[年月]
		RETURN
			'F01月末一次加权均法'[入库金额]*
			PRODUCTX(
				FILTER(
					'F01月末一次加权均法',
					'F01月末一次加权均法'[年月]>=vNowDateInner
				),
				'F01月末一次加权均法'[保留率]
            )
			
	),
	'F01月末一次加权均法'[年月]<=vNowDate,
	ALL('D00日期表')
)