仿因素分析法，用Power BI分析比率类指标变化的一种思路，及其具体的DAX实现方法。

最新推荐文章于 2024-07-14 13:40:38 发布

芦骁骏

最新推荐文章于 2024-07-14 13:40:38 发布

阅读量1k

点赞数 18

文章标签： powerbi 数据分析 excel

本文链接：https://blog.csdn.net/weixin_38775843/article/details/140238539

版权

使用此方法可以较为准确便捷地了解前后毛利率差异所在。是因为老客户流失，还是因为新增客户的毛利率异常，又或者是存续客户的毛利率或业务量出现变化。且能将这些因素进行量化，具体细分到类别，并细分到客户。如下图所示：
Pasted image 20240706225714|500

[!TIP] 该图表展示差异所在，但未毕是差异原因，具体原因还需要结合业务情况具体分析。

1. 因素分析法介绍

因素分析法是依据财务指标与其驱动因素之间的关系，从数量上确定各因素对指标影响程度的分析方法。由于在分析时，要逐次进行各因素的有序替代，因此又称为连环替代法。

以下示例很简明的讲解这种方法的用法。

比较值：M1＝A1×B1×C1  
基准值：M0＝A0×B0×C0  
差异值：ΔM＝M1－M0  

分析过程如下：  
基准值： M0＝A0×B0×C0  
第①次替代（替代A因素）：A1×B0×C0  
第②次替代（替代B因素）：A1×B1×C0  
第③次替代（替代C因素）：A1×B1×C1＝M1（比较值）  

A因素变动对M的影响＝替代①－基准值M0＝A1×B0×C0－A0×B0×C0＝（A1－A0）×B0×C0  
B因素变动对M的影响＝替代②－替代①＝A1×B1×C0－A1×B0×C0＝A1×（B1－B0）×C0  
C因素变动对M的影响＝替代③（比较值M1）－替代②＝A1×B1×C1－A1×B1×C0＝A1×B1×（C1－C0）

2. 毛利率变化分析思路

毛利率是一个比率指标，分子分母的变化都会影响到结果，要分析其变化会有些棘手。有一种分析思路是，毛利率的变化可以分为两个原因：

几个客户的毛利率变高或者变低了。
几个客户的业务量比重变高或者变化了。
第2.条又可以细分为老客户流失、存续客户的业务量变化、新客户的加入，这样3条。

2.1. 整体毛利率与各部分毛利率的关系

很显而易见，整体毛利率就是各部分毛利率的平均值。但是，需要加权平均，权重为其收入的比重。

$\frac{\sum_{i=1}^n 毛利_i }{\sum_{j=1}^n 收入_i }= \sum_{i=1}^n (\frac{毛利_i }{\sum_{j=1}^n 收入_i }) =\sum_{i=1}^n (\frac{毛利_i }{收入_i} \times \frac{收入_i }{\sum_{j=1}^n 收入_i}) =\sum_{i=1}^n (毛利率_i \times 收入占比_i)$

2.2. 毛利率分析思路与因素分析法的结合

如下图示意了毛利率在前后月间的变化，图1：
Pasted image 20240706221548|600

使用因素分析法，其变化过程图解如下，图2：
Pasted image 20240706221958|1000

3. 使用DAX实现如上过程

示例文件下载：
链接：百度网盘

3.1. 数据准备

已准备数据如下：
Pasted image 20240706230625|500
Pasted image 20240706230657|500
Pasted image 20240706230716|500
Pasted image 20240706230800|500

3.2. 建立基础度量值

毛利率 = DIVIDE(SUM('毛利分析'[毛利]),SUM('毛利分析'[收入]))

毛利率.上月 = CALCULATE([毛利率],PARALLELPERIOD('日期表'[日期],-1,MONTH))

收入占比 = DIVIDE(SUM('毛利分析'[收入]),CALCULATE(SUM('毛利分析'[收入]),ALLSELECTED('客户'[客户])))

收入占比.上月 = CALCULATE([收入占比],PARALLELPERIOD('日期表'[日期],-1,MONTH))

3.3. 套用因素分析法

在DAX查询视图下，以2024年4月为例编写表达式。待测试没问题后，只要再稍加修改，就成为可在可视化对象中使用的度量值。

3.3.1. 实现图1

DEFINE
VAR YearMonth=DATE(2024,4,1)
VAR ProfitRateV0=CALCULATE([毛利率.上月],ALL(),VALUES('日期表'[年月]))
VAR FilterTable1=TREATAS({YearMonth,EOMONTH(YearMonth,-2)+1 },'日期表'[年月])
//提取出前后两个月有业务的客户
VAR t1 = 
	SUMMARIZE(
		CALCULATETABLE(ALLSELECTED('毛利分析'),FilterTable1),
		'客户'[客户]
	)
//前后两个月的相关数据
VAR t2=
	CALCULATETABLE(
		ADDCOLUMNS(
			t1,
			"收入占比",[收入占比],
			"毛利率",[毛利率],
			"收入占比.上月",[收入占比.上月],
			"毛利率.上月",[毛利率.上月]
		),
		'日期表'[年月]=YearMonth,
		ALLSELECTED()
	)
EVALUATE t2

Pasted image 20240706231847|600

3.3.2. 实现图2中g1，计算老客户流失后的情况

Pasted image 20240706232018|400

增加如下代码：

//上月数据有数据，本月有数据
VAR t3=FILTER(t2,[收入占比.上月]<>BLANK()&&[收入占比]<>BLANK()) 
//上月剩余收入占比总和，失去上月客户
VAR SumIncPre=SUMX(t3,[收入占比.上月])
//修正上月收入占比，失去上月客户
VAR t4=ADDCOLUMNS(t3,"收入占比.上月.调整",[收入占比.上月]/SumIncPre)
//失去上月老客户后的毛利率。（上月的比重与上月的毛得率）
VAR ProfitRateV1=SUMX(t4,[收入占比.上月.调整]*[毛利率.上月])

得到如下结果：
Pasted image 20240706232234|600

注意：这里使用的是收入占比是调整过的。因为去掉流失客户后，原收入占比总计不等于1，加权平均后的总体毛利率会小于实际值。

3.3.3. 计算这次因素替代的差异,并将差异摊入各客户

//第一次调整的差异
VAR Diff1=ProfitRateV1-ProfitRateV0
//第一次差异明细,以收入占比乘以毛利率差为权重进行分摊。
VAR DetailData1=FILTER(t2,[收入占比.上月]<>BLANK()&&[收入占比]=BLANK()) 
VAR SumIncDet1=SUMX(DetailData1,[收入占比.上月]*([毛利率.上月]-ProfitRateV0))
VAR Detail1=SELECTCOLUMNS(ADDCOLUMNS(DetailData1,"分摊差异",[收入占比.上月]*([毛利率.上月]-ProfitRateV0)/SumIncDet1*Diff1),"客户",[客户],"年月",YearMonth,"差异原因","失去老客户","分摊差异",[分摊差异])

这里我们使用 [收入占比.上月]*( [毛利率.上月] - ProfitRateV0 ) 作为权重，对g0改g1产生的总差异进行分摊，因为它真实的体现了流失的老客户的业务量与它们的毛利率的影响。

至此，我们得到了第一步替换的差异明细：
Pasted image 20240706233627|600

3.3.4. 实现图2中g2，计算存续客户收入占比改变产生的差异。

Pasted image 20240706234141|600

VAR SumIncCur=SUMX(t3,[收入占比])
//修正收入占比，失去上月客户，不考虑新客户。
VAR t5=ADDCOLUMNS(t4,"收入占比.调整",[收入占比]/SumIncCur)
//比重改变后的毛利率,及第二次调整的差异。（本月的比重与上月的毛得率）
VAR ProfitRateV2=SUMX(t5,[收入占比.调整]*[毛利率.上月])
VAR Diff2=ProfitRateV2-ProfitRateV1
//计算结构变化对毛利的影响
VAR t6=ADDCOLUMNS(
		ADDCOLUMNS(t5,"毛利乘权重.原",[收入占比.上月.调整]*[毛利率.上月],"毛利乘权重.调比重",[收入占比.调整]*[毛利率.上月],"毛利乘权重.调比重调毛利",[收入占比.调整]*[毛利率]),
		"分摊差异",[毛利乘权重.调比重]-[毛利乘权重.原]
	)
VAR Detail2=SELECTCOLUMNS(t6,"客户",[客户],"年月",YearMonth,"差异原因","收入比重变化","分摊差异",[分摊差异])

这里 收入占比 也使用调整过的，原因同上。

至此，可以得到如下结果：
Pasted image 20240706234420|600
上图中 分摊差异 显示为0是因为数据格式的原因。如果我们将 Detail2 定义中的 [分摊差异] 改为 FORMAT([分摊差异],"0.00%") 便可看到具体数值了。但这样，就变成了文本。

3.3.5. 实现图2中g3，计算存续客户毛利率改变产生的差异。

Pasted image 20240706234955|600

//毛利率改变后的毛利率,及第三次调整的差异。（上月的比重与本月的毛得率）
VAR ProfitRateV3=SUMX(t6,[收入占比.调整]*[毛利率])
VAR Diff3=ProfitRateV3-ProfitRateV2
VAR t7=ADDCOLUMNS(t6,"分摊差异2",[毛利乘权重.调比重调毛利]-[毛利乘权重.调比重]
	)
VAR Detail3=SELECTCOLUMNS(t7,"客户",[客户],"年月",YearMonth,"差异原因","毛利率变化","分摊差异",[分摊差异2])
//计算本月新客户对毛利率的影响。以上变化再加入新客户，也就是本月的实际毛利率了。

Pasted image 20240706235216|600

3.3.6. 实现图2中g4，计算加入新客户后的差异，并将差异摊入各客户

Pasted image 20240706235638|600

//计算本月新客户对毛利率的影响。以上变化再加入新客户，也就是本月的实际毛利率了。
VAR ProfitRateV4 = SUMX(t2,[收入占比]*[毛利率])
//第四次调整的差异
VAR Diff4=ProfitRateV4-ProfitRateV3
//第四次差异明细,以收入占比乘以毛利率差为权重进行分摊。
VAR DetailData2=FILTER(t2,[收入占比.上月]=BLANK()&&[收入占比]<>BLANK()) 
VAR SumIncDet2=SUMX(DetailData2,[收入占比]*([毛利率]-ProfitRateV3))
VAR Detail4=SELECTCOLUMNS(ADDCOLUMNS(DetailData2,"分摊差异",[收入占比]*([毛利率]-ProfitRateV3)/SumIncDet2*Diff4),"客户",[客户],"年月",YearMonth,"差异原因","新客户加入","分摊差异",[分摊差异])

这里我们使用 [收入占比]*([毛利率]-ProfitRateV3) 作为权重，对g3改g4产生的总差异进行分摊，原因与之前出现过的一样，因为它真实地体现了加入客户的业务量与它们的毛利率的影响。
得到如下结果：
Pasted image 20240706235823|600

3.3.7. 合并以上4个差异明细

VAR DiffDetail=UNION(Detail1,Detail2,Detail3,Detail4)

Pasted image 20240707000006|600

3.3.8. 在上表的基础上编写在可视化对象中使用度量值

VAR Diff=
	SUMX(
		VALUES('毛利率差异原因'[差异原因]),
		SUMX(
			VALUES('客户'[客户]),
			SUMX(
				FILTER(
					DiffDetail,
					[差异原因]='毛利率差异原因'[差异原因]&&
                        [客户]='客户'[客户]
				),
				[分摊差异]
			)
		)
	)

3.4. 建立可视化对象

将DAX查询视图代码中的测试用的 DATE(2024,4,1) 替换为 SELECTEDVALUE('日期表'[年月])，成为正式的度量值。如下图：
Pasted image 20240707000543|600
完整的度量值：

差异 = 
VAR YearMonth=SELECTEDVALUE('日期表'[年月])
VAR ProfitRateV0=CALCULATE([毛利率.上月],ALLSELECTED(),VALUES('日期表'[年月]))
VAR FilterTable1=TREATAS({YearMonth,EOMONTH(YearMonth,-2)+1 },'日期表'[年月])
//提取出前后两个月有业务的客户
VAR t1 = ALLSELECTED('客户'[客户])
	// SUMMARIZE(
	// 	CALCULATETABLE(ALLSELECTED('毛利分析'),FilterTable1),
	// 	'客户'[客户]
	// )
//前后两个月的相关数据
VAR t2=
	CALCULATETABLE(
		ADDCOLUMNS(
			t1,
			"收入占比",[收入占比],
			"毛利率",[毛利率],
			"收入占比.上月",[收入占比.上月],
			"毛利率.上月",[毛利率.上月]
		),
		'日期表'[年月]=YearMonth,
		ALLSELECTED()
	)
//上月数据有数据，本月有数据
VAR t3=FILTER(t2,[收入占比.上月]<>BLANK()&&[收入占比]<>BLANK()) 
//上月剩余收入占比总和，失去上月客户
VAR SumIncPre=SUMX(t3,[收入占比.上月])
//修正上月收入占比，失去上月客户
VAR t4=ADDCOLUMNS(t3,"收入占比.上月.调整",[收入占比.上月]/SumIncPre)
//失去上月老客户后的毛利率。（上月的比重与上月的毛得率）
VAR ProfitRateV1=SUMX(t4,[收入占比.上月.调整]*[毛利率.上月])
//第一次调整的差异
VAR Diff1=ProfitRateV1-ProfitRateV0
//第一次差异明细,以收入占比乘以毛利率差为权重进行分摊。
VAR DetailData1=FILTER(t2,[收入占比.上月]<>BLANK()&&[收入占比]=BLANK()) 
VAR SumIncDet1=SUMX(DetailData1,[收入占比.上月]*([毛利率.上月]-ProfitRateV0))
VAR Detail1=SELECTCOLUMNS(ADDCOLUMNS(DetailData1,"分摊差异",[收入占比.上月]*([毛利率.上月]-ProfitRateV0)/SumIncDet1*Diff1),"客户",[客户],"年月",YearMonth,"差异原因","失去老客户","分摊差异",[分摊差异])
//本月剩余收入占比总和，失去上月客户，不考虑新客户。
VAR SumIncCur=SUMX(t3,[收入占比])
//修正收入占比，失去上月客户，不考虑新客户。
VAR t5=ADDCOLUMNS(t4,"收入占比.调整",[收入占比]/SumIncCur)
//比重改变后的毛利率,及第二次调整的差异。（本月的比重与上月的毛得率）
VAR ProfitRateV2=SUMX(t5,[收入占比.调整]*[毛利率.上月])
VAR Diff2=ProfitRateV2-ProfitRateV1
//计算结构变化对毛利的影响
VAR t6=ADDCOLUMNS(
		ADDCOLUMNS(t5,"毛利乘权重.原",[收入占比.上月.调整]*[毛利率.上月],"毛利乘权重.调比重",[收入占比.调整]*[毛利率.上月],"毛利乘权重.调比重调毛利",[收入占比.调整]*[毛利率]),
		"分摊差异",[毛利乘权重.调比重]-[毛利乘权重.原]
	)
VAR Detail2=SELECTCOLUMNS(t6,"客户",[客户],"年月",YearMonth,"差异原因","收入比重变化","分摊差异",[分摊差异])
//毛利率改变后的毛利率,及第三次调整的差异。（上月的比重与本月的毛得率）
VAR ProfitRateV3=SUMX(t6,[收入占比.调整]*[毛利率])
VAR Diff3=ProfitRateV3-ProfitRateV2
VAR t7=ADDCOLUMNS(t6,"分摊差异2",[毛利乘权重.调比重调毛利]-[毛利乘权重.调比重]
	)
VAR Detail3=SELECTCOLUMNS(t7,"客户",[客户],"年月",YearMonth,"差异原因","毛利率变化","分摊差异",[分摊差异2])
//计算本月新客户对毛利率的影响。以上变化再加入新客户，也就是本月的实际毛利率了。
VAR ProfitRateV4 = SUMX(t2,[收入占比]*[毛利率])
//第四次调整的差异
VAR Diff4=ProfitRateV4-ProfitRateV3
//第四次差异明细,以收入占比乘以毛利率差为权重进行分摊。
VAR DetailData2=FILTER(t2,[收入占比.上月]=BLANK()&&[收入占比]<>BLANK()) 
VAR SumIncDet2=SUMX(DetailData2,[收入占比]*([毛利率]-ProfitRateV3))
VAR Detail4=SELECTCOLUMNS(ADDCOLUMNS(DetailData2,"分摊差异",[收入占比]*([毛利率]-ProfitRateV3)/SumIncDet2*Diff4),"客户",[客户],"年月",YearMonth,"差异原因","新客户加入","分摊差异",[分摊差异])
VAR DiffDetail=UNION(Detail1,Detail2,Detail3,Detail4)
VAR DiffAll=SUMX(DiffDetail,[分摊差异])
VAR Diff=
	SUMX(
		VALUES('毛利率差异原因'[差异原因]),
		SUMX(
			VALUES('客户'[客户]),
			SUMX(
				FILTER(
					DiffDetail,
					[差异原因]='毛利率差异原因'[差异原因]&&
                        [客户]='客户'[客户]
				),
				[分摊差异]
			)
		)
	)
RETURN Diff

4. 进一步优化的思路

因素分析法的分析结果，往往会受到替代顺序的影响。
如果我们改用这样的替代顺序的话，就会得到不一样的结果：
Pasted image 20240707001558|600
这个例子比较复杂，为了说明这种影响，我们换一个简单的例子：

甲公司有A和B两个部门。A部门负责引流，B部门负责转化率。2023年引流10000人，转化率10%，实现1000单。2024年引流12000，转化率15%，实现1800单。

如果我们先替代引流人数，分析结果为：A部门实现订单增涨 (12000-1000)*10%=200 单，B部门实现 12000*(15%-10%)=600 单。
如果我们先替代转化率，分析结果为：B部门实现订单增涨 10000*(15%-10%)=500 单，A部门实现 (12000-1000)*15%=300 单。

我们可以看到，后被替代的因素会受到先被替代的因素的影响，而被放大或缩小自身的影响。

如果我们只是分析的需要的话，我们可以根据变化的先后顺序决定因素替代的顺序。或者将产生差异大的因素替代后置。如上例，则将B部门（转化率）的替代放在后面，先替代其他因素。这样使得其他素因受到较小的影响。

如果这种分析要用于考核，则需要考虑其公平性。在这里，我们可以先计算其各自先替代，所产生的差异。比如：先替代A部门（引流人数），200。先替代B部门（转化率），500。再根据计算结果分摊总的差异。A部门 200/(200+500)*800，B部门 500/(200+500)*800。

5. 文件下载

下载示例文件：百度网盘

芦骁骏

关注

18
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
仿因素分析法，用Power BI分析比率类指标变化的一种思路，及其具体的DAX实现方法。

在Power BI中使用DAX实现因素分析法。结构可以当作一项变量。
复制链接

扫一扫