PowerBI HR组织架构层级通用计算模型-技术圈

在企业组织中，涉及到非常多和组织架构层级有关的计算，本文给出通用计算模型。

这种模型具有两个特点：

表的递归关系
递归关系展开

先来看一个示例。

表示一个公司

一般用如下结构表示一个公司：

公司的结构本质是递归的，也就是在一个表中，除了当前节点，还要指出它的上级节点。

表示所有员工

一般用如下结构表示员工以及某种KPI：

现在的问题是如何根据各个员工的部门归属来计算不同部门以及父级部分的KPI。

效果展示

首先给出效果上的展示，如下：

右边给出两种可能的展示效果：

部门汇总型

给出直接管理人员的KPI
给出包括子级所有人员的KPI

人员独立型

给出不同人员的单独KPI

层级展开的实现方法

表的递归关系在表示组织结构的时候非常有效，但是我们往往需要它的展开型结构，这就需要对递归进行展开。

PowerBI 的 DAX 非常强大，给了相关函数可以直接使用，非常轻松。

第一步，建立计算列，得到递归的展开路径，用 PowerBI DAX 实现，如下：

Path = PATH( tbOrg[ID] , tbOrg[PID] )

结果如下：

注意：Path 不是度量值，是计算列。

这样，就知道对于任何一个 Item，它在层级中的实际位置。

第二步，对任何一个 Item，建立计算列，表示层级。用 PowerBI DAX 实现，如下：

L1 = PATHITEM( [Path] , 1 , INTEGER )
L1Name = LOOKUPVALUE( [Item] , [ID] , PATHITEM( [Path] , 1 , INTEGER ) ) 

L2 = PATHITEM( [Path] , 2 , INTEGER )
L2Name = LOOKUPVALUE( [Item] , [ID] , PATHITEM( [Path] , 2 , INTEGER ) ) 

L3 = PATHITEM( [Path] , 3 , INTEGER )
L3Name = LOOKUPVALUE( [Item] , [ID] , PATHITEM( [Path] , 3 , INTEGER ) ) 

L4 = PATHITEM( [Path] , 4 , INTEGER )
L4Name = LOOKUPVALUE( [Item] , [ID] , PATHITEM( [Path] , 4 , INTEGER ) )

注意：有学习了BI佐罗 DAX 基础的伙伴会看出来，L1，L2，L3，L4 不带 Name 的命名，是可以作为按列排序的排序列的，而带有 Name 命名的列才是名称列。因此，对于一个层级就需要建立一对计算列，表示排序和名称。

本案例分别建立了 8 个计算列，效果如下：

注意：以上内容全部由计算列构成。

问题来了：

我们怎么知道要展开到多少级呢？
展开一级就创建两个计算列，那有10个层级，难道要创建20个计算列吗？

对于递归的层级问题，理论上可以展开很多，但考虑到真实的业务环境，一个企业或某种系统，一般不会有这么多层级，不然是很难管理的，这就保证了实际当中，这种展开往往达到四个层级就比较多了。这也就确保了即使有一定的手工工作量来创建计算列也是可以接受的，这样就完成了层级的展开。

小结：

要实现递归表的层级展开，总共分两步：

创建计算列，用 PATH 函数计算展开路径
创建计算列，根据展开路径展开

实现层级计算

以本例为例，要实现层级计算。

第一步，建立数据模型：

第二步，创建度量值：

用 PowerBI DAX 实现，如下：

KPI.Core = SUM( tbUser[KPI] )

KPI.Scope = 
SWITCH( TRUE(),
    SELECTEDVALUE( tbOrg[L4Name] ) <> BLANK() , [KPI.Core] ,
    SELECTEDVALUE( tbOrg[L3Name] ) <> BLANK() , SUMX( ALL( tbOrg[L4Name] , tbOrg[L4] ) , [KPI.Core] ) ,
    SELECTEDVALUE( tbOrg[L2Name] ) <> BLANK() , SUMX( ALL( tbOrg[L4Name] , tbOrg[L4] , tbOrg[L3Name] , tbOrg[L3] ) , [KPI.Core] ) ,
    SELECTEDVALUE( tbOrg[L1Name] ) <> BLANK() , SUMX( ALL( tbOrg[L4Name] , tbOrg[L4] , tbOrg[L3Name] , tbOrg[L3] , tbOrg[L2Name] , tbOrg[L2] ) , [KPI.Core] ) ,
    BLANK()
)