DAX 实现公摊金额按比例分摊

btharp

发布于 2022-04-19 09:40:18

8630

发布于 2022-04-19 09:40:18

文章被收录于专栏：PowerBI x PythonPowerBI x Python

问题情况

1、两张表（销售事实表和部门小组维度表），表间必须按小组关联。需要用维度表做透视、筛选。

2、事实表中，有XX小组公摊和部门公摊，这些不在小组维度表上。

3、销售额没有公摊，而成本有公摊

4、要求将成本的小组公摊，按小组收入占该部门比，分摊进小组；将部门公摊，按小组收入占总收入比，分摊进各小组。示意如下：

5、只能在power pivot 里做，因此有些DAX函数缺失，比如treatas

解决方案

1、小组比例

小组比例:=divide(sum(事实表[销售额]),
      CALCULATE(sum(事实表[销售额]),ALL('部门小组'[小组])),
      0)

2、部门比例

小组比例:=divide(sum(事实表[销售额]),
      CALCULATE(sum(事实表[销售额]),ALL('部门小组')),
      0)

3、处理分摊

我们转化一下这个问题，相当于我们要求成本度量值，使得下式成立：

成本 = 原成本 + 小组分摊 + 部门分摊 - 公摊值

意思是，每个非公摊小组加上公摊部分之后，我们还需要使得公摊小组的值为0。

那么公摊分摊的基本解决思路是，切断事实表和维度表小组之间组与组织间的关系，让事实表的非公摊小组，对应上维度表的公摊小组。

由于事实表和维度表必须关联，以及power pivot 度量值缺失的限制，所以，在做分摊的时候我们需要另辟蹊径。

我的做法是，在分别在事实表和维度表上增加两个计算列：小组公摊和部门公摊。示意图如下：

然后用crossfilter切断原有的关联，再用intersect把两个表通过小组公摊这个新增计算列关联起来。

此时，小组公摊的度量值可以这么写：

小组公摊:=
calculate(sum(事实表[成本]）,
    CROSSFILTER('部门小组'[小组],'销售账单'[统一小组],None),
    intersect(all('事实表'[小组公摊]),values('部门小组'[小组公摊]))
    )

部门公摊同理。得到的结果如下：

接着，分别乘以小组比例和部门比例，即可求出每个小组的公摊值。我们再添加一个是否公摊的计算列来判断是否需要减去公摊值。最后，完整度量值如下：

成本:=
var cost=sum(事实表[成本])
var team=calculate(sum(事实表[成本]),
      CROSSFILTER('部门小组'[小组],'销售账单'[统一小组],None),
      intersect(all('事实表'[小组公摊]),values('部门小组'[小组公摊]))
      )
var dept=calculate(sum(事实表[成本]),
      CROSSFILTER('部门小组'[小组],'销售账单'[统一小组],None),
      intersect(all('事实表'[部门公摊]),values('部门小组'[部门公摊]))
      )
var public=calculate(sum(事实表[成本]),
        filter('事实表','事实表'[是否公摊]="是")
        )
return
cost + team*[小组比例] + dept*[部门比例] - public

最终结果：