实战之预算分摊终极模板
实战之预算分摊终极模板
现实工作中，每个领导都有不同的见解，财务计划部门需要把这些想法汇总起来呈现给总经理，而总经理又会在此基础上不断的调和，修正；反映在财务计划部门的直观感受便是调不完的数和不断推翻的假设。如果我们能做到对POWER BI中Query和Power Pivot的灵活运用的话，就能极大的解放财务小伙伴的生产力，并把释放出来的精力投入到更激烈地争吵中去。
下面我们就以一组Northwind虚拟公司数据的实战案例来给大家分享一个很有用的预算分摊模板。
进入正题
概念：所有公司的预算都是数据在多个维度之间的组合分摊，比如说销量预算分区域分产品类型，费用预算分科目组和成本中心，成本预算分车间和工作中心等。而不同级别的领导，所需要看到的数据的颗粒度都是不一样的。
比如销量预算，总经理只想看到每个销售总监的年度预算，而销售总监想要看到每个业务员的预算，或者突然又想看到每个业务员负责的区域的客户的销售的产品的预算，就像大雁南飞，一下排成十字型，一下又排成一字型，让你十分地想死。
我们先从一个稍微有点数字逻辑且比较靠谱的领导案例下手：
情景一
假设领导明年预算指标想要按照每个国家，城市，并且分月来制定年度预算；
我们先在历史数据中选择一年作为预算底表，用来做对比分析：
领导们的预算颗粒度 =
ADDCOLUMNS (
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Northwind Customers'[Country],
'Northwind Customers'[City],
'Calendar'[Month],
"历史销量", [Invoiced Quantity]
),
'Calendar'[Year] = 1998
),
"预算销量", [历史销量] * 1.2
)你实际上获得了一张预算标准填报表，可以利用我上一篇文章：“实战案例-动态分类洞察数据”中的套路一来随时修改想要的预算数值，或者做成预算收集表发给部门领导下级填报，在这便不展开了，以后可以单独讲其间涉及到的Power Query 甚至Power App的应用；
情景二
假设总经理已经在刚才的颗粒度上拍板预算指标了，部门领导突然觉得这个指标还不够具体，要求再增加到每种产品的预算指标；这时候表哥表姐们就得闪亮登场了。怎么办？再来一个表函数：
表哥表姐们需要拆分到的预算颗粒度 =
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Northwind Customers'[Country],
'Northwind Customers'[City],
'Northwind Products'[ProductName],
'Calendar'[Month],
"销量", [Invoiced Quantity]
),
'Calendar'[Year] = 1998
)
可以看到对仗工整的另外一个辅助表，在历史数据中多取了一个维度：产品；
情景三
将总经理拍板的预算颗粒度Dax表达式写出来，这也是这篇文章中的第一个DAX套路：
领导们的预算颗粒度销量 =
CALCULATE (
SUM ( '领导们的预算颗粒度'[预算销量] ),
TREATAS (
SUMMARIZE (
'表哥表姐们需要拆分到的预算颗粒度',
'表哥表姐们需要拆分到的预算颗粒度'[City],
'表哥表姐们需要拆分到的预算颗粒度'[Country],
'表哥表姐们需要拆分到的预算颗粒度'[Month]
),
'领导们的预算颗粒度'[City],
'领导们的预算颗粒度'[Country],
'领导们的预算颗粒度'[Month]
)
)Calculate(Topline预算值表达式，TREATAS(SUMMARIZE(表哥辅助表，领导维度1，领导维度2，领导维度3...),领导预算表[领导维度1],领导预算表[领导维度2],领导预算表[领导维度3]...)
更细一级的分摊系数计算出来，用一个简单的除法：
预算分摊系数-销量 =
VAR BudgetTopline =
CALCULATE (
SUM ( '表哥表姐们需要拆分到的预算颗粒度'[销量] ),
KEEPFILTERS (
SUMMARIZE (
'表哥表姐们需要拆分到的预算颗粒度',
'表哥表姐们需要拆分到的预算颗粒度'[City],
'表哥表姐们需要拆分到的预算颗粒度'[Country],
'表哥表姐们需要拆分到的预算颗粒度'[Month]
)
),
ALL ( '表哥表姐们需要拆分到的预算颗粒度' )
)
VAR BudgetDetailedLevel =
CALCULATE ( SUM ( '表哥表姐们需要拆分到的预算颗粒度'[销量] ) )
RETURN
DIVIDE (BudgetDetailedLevel, BudgetTopline )这是一个极其工整简洁的写法，通过TREATAS实现数据沿袭，正如英文字面意思一样——“把……当作……”,不需要任何的表关系便可以实现多对多不同颗粒度表之间的运算；如果有童鞋需要在Excel里实现的话，TREATAS便不能用了，得用Filter+Contains代替；
情景四
我们现在需要将比总经理预算颗粒度
预算分摊系数-销量 =
VAR BudgetTopline =
CALCULATE (
SUM ( '表哥表姐们需要拆分到的预算颗粒度'[销量] ),
KEEPFILTERS (
SUMMARIZE (
'表哥表姐们需要拆分到的预算颗粒度',
'表哥表姐们需要拆分到的预算颗粒度'[City],
'表哥表姐们需要拆分到的预算颗粒度'[Country],
'表哥表姐们需要拆分到的预算颗粒度'[Month]
)
),
ALL ( '表哥表姐们需要拆分到的预算颗粒度' )
)
VAR BudgetDetailedLevel =
CALCULATE ( SUM ( '表哥表姐们需要拆分到的预算颗粒度'[销量] ) )
RETURN
DIVIDE (BudgetDetailedLevel, BudgetTopline )
本文的第二个DAX套路模板便是：KEEPFILTERS(SUMMARIZE(表哥维度1，2，3...),All(表哥辅助表));
这同样是一个非常简洁的写法，通过All 和 Keepfilters的配合，保留领导的预算颗粒度，清除表哥的预算颗粒度，来识别出那些是Topline的预算维度，哪些是Bottomline的预算维度，然后这两者相除得到了多维立体的分摊系数；
情景五
接下来就顺理成章了：
我们将预算维度从高到底依次排开在矩阵表里：（注意维度需要在表哥辅助表里选择）
下图可以大致看出计算的思路和过程：分摊系数只有在领导预算颗粒度级别往下才为小数，其他的全为1，因此得到了正确的结果。
全程只要五分钟！干净清爽，快速无痛！
情景六
No, Wait! 我想要矩阵表的底数好上传到系统里或者转发给其他人！
数据量较小的情况下可以直接点导出数据CSV文件。但往往实际情况是超出POWER BI的导出行数限制，因为表哥表姐们还想将它分摊到客户，业务员等等。
通过如上图示步骤导出内部的DAX表达式，并粘贴进Dax Studio里。将TOPN 里的501后面加上几个0，你就可以拥有一切了。
// DAX Query
...(以上省略）
EVALUATE
TOPN(
501, //POWER BI 生成的表达式只取TOP 501 个
__DS0PrimaryWithSortColumnsOutputTotals,
[SortBy_DM0_0],
0,
'表哥表姐们需要拆分到的预算颗粒度'[Country],
1,
[SortBy_DM1_0],
0,
'表哥表姐们需要拆分到的预算颗粒度'[City],
1,
[SortBy_DM2_0],
0,
'表哥表姐们需要拆分到的预算颗粒度'[Month],
1,
[预算分摊系数_销量],
0,
'表哥表姐们需要拆分到的预算颗粒度'[ProductName],
1
)
ORDER BY
[SortBy_DM0_0] DESC,
'表哥表姐们需要拆分到的预算颗粒度'[Country],
[SortBy_DM1_0] DESC,
'表哥表姐们需要拆分到的预算颗粒度'[City],
[SortBy_DM2_0] DESC,
'表哥表姐们需要拆分到的预算颗粒度'[Month],
[预算分摊系数_销量] DESC,
'表哥表姐们需要拆分到的预算颗粒度'[ProductName]
总结
该套路看似简单，实际上包含了很多技巧，包括辅助表的选用，不同颗粒度级别的甄别与筛选，最好结合自己的数据源联系一遍方可灵活掌握，不断拓展；最终可以彻底解放双手，改打嘴仗!
注：在上一篇数据分类套路实战中，有网友提出的数据分类另一种解法，更加直击灵魂，经传统非实战测试没有任何坑，特补充分享：
变动分类 =
VAR class =
COUNTROWS (
FILTER (
'变动情况分类',
[Invoiced Quantity YoY%] > [低]
&& [Invoiced Quantity YoY%] <= [高]
)
) > 0
RETURN
SWITCH ( TRUE (), class, [Invoiced Quantity YoY%] )*以上示例文件可点原文链接获取。
提取码：ven8