请在文末下载附件
一、背景
之前有帮公司HR做了些员工入离调转、在职、人工成本分析等（体量：4000人左右）。在和其他朋友交流的时候得知，貌似这些指标在Excel中写出来比较臃肿，用表格公式做起来会比较麻烦且时间维度的交互比较难。
二、案例
在上面背景下，今天写个入离调转、在职、离职率这几个简单的指标，至于人工成本 、人员结构等分析后续有好的素材在写，这些模拟数据太麻烦了。闲话不多讲。先看下结果图。
基础数据
1、花名册
2、人员异动
3、部门表
4、日期表
建立关系如下：
三、度量值
1、习惯先把要用的度量值写出来，便于后续使用且不易出所谓“BUG”
花名册总人数
花名册总人数:=DISTINCTCOUNT('花名册'[ID])异动总人数
异动总人数:=DISTINCTCOUNT('人员异动'[ID])2、在上述的关系下，先把几个简单的写出来
入职
入职:=CALCULATE('花名册'[花名册总人数])离职
由于日期建立虚线关系，需要使用userelationship
离职:=CALCULATE('花名册'[花名册总人数],USERELATIONSHIP('花名册'[离职日期],'日期表'[日期]))异入
异入:=CALCULATE('人员异动'[异动总人数])异出
同理，异出部门建立虚线关系，需要使用userelationship
异出:=CALCULATE('人员异动'[异动总人数], USERELATIONSHIP('人员异动'[异出部门],'部门'[部门]))3、比较难处理的是在职的人数，主要考虑在职的定义（在职=累计入职-累计离职+累计异入-累计异出）和时间上下文
在职
在职:=VAR T =
VAR DT1 =MAX ( '日期表'[日期] )
VAR DT2 =CALCULATE ( MIN ( '日期表'[日期] ), ALL ( '日期表' ) )
RETURN
DATESBETWEEN ( '日期表'[日期], DT2, DT1 )//创建时间上下文条件
VAR RZ =CALCULATE ( '花名册'[花名册总人数], T )//累计入职人数
VAR LZ =CALCULATE ( '花名册'[花名册总人数], T, USERELATIONSHIP ( '花名册'[离职日期], '日期表'[日期] ) )//累计离职人数
VAR YR =CALCULATE ( '人员异动'[异动总人数], T )//累计异入人数
VAR YC =CALCULATE ( '人员异动'[异动总人数], T, USERELATIONSHIP ( '人员异动'[异出部门], '部门'[部门] ) )//累计异出人数
RETURN
RZ - LZ- YC + YR
月离职率
离职率定义有很多种，作如下定义：当月离职人数/当月累计在职人数（包含离职），
月离职率:=VAR T =
VAR DT1 =MAX ( '日期表'[日期] )
VAR DT2 =CALCULATE ( MIN ( '日期表'[日期] ), ALL ( '日期表' ) )
RETURN
DATESBETWEEN ( '日期表'[日期], DT2, DT1 )//创建累计时间上下文
VAR T1 =
VAR DT1 =DATEADD ( STARTOFMONTH ( '日期表'[日期] ), -1, DAY )
VAR DT2 =CALCULATE ( MIN ( '日期表'[日期] ), ALL ( '日期表' ) )
RETURN
DATESBETWEEN ( '日期表'[日期], DT2, DT1 )//创建月度离职率特定时间上下文
VAR RZ =CALCULATE ( '花名册'[花名册总人数], T )
VAR LJLZ =CALCULATE ( '花名册'[花名册总人数], T1, USERELATIONSHIP ( '花名册'[离职日期], '日期表'[日期] ) )
VAR LZ =CALCULATE ( '花名册'[花名册总人数], USERELATIONSHIP ( '花名册'[离职日期], '日期表'[日期] ) )
VAR YR =CALCULATE ( '人员异动'[异动总人数], T )
VAR YC =CALCULATE ( '人员异动'[异动总人数], T, USERELATIONSHIP ( '人员异动'[异出部门], '部门'[部门] ) )
RETURN
DIVIDE ( LZ, RZ - LJLZ - YC + YR )
来一个时间选择2018年9月定格的最终结果。
四、总结
1、指标要服务业务，要理解业务；
2、虚线关系userelationship的使用；
3、构造特定业务的上下文，这个案例主要是时间类的上下文。
五、修正
由于之前没有考虑到异动带来的离职归属到异入部门的问题，特做修正。
1、在花名册中加入字段【@离职部门】的概念；
2、【@离职部门】与部门建立关系
3、度量修正如下：
离职
离职:=CALCULATE('花名册'[花名册总人数],USERELATIONSHIP('花名册'[离职日期],'日期表'[日期]),USERELATIONSHIP('花名册'[@离职部门],'部门'[部门]))
在职
在职:=VAR T =
VAR DT1 =MAX ( '日期表'[日期] )
VAR DT2 =CALCULATE ( MIN ( '日期表'[日期] ), ALL ( '日期表' ) )
RETURN
DATESBETWEEN ( '日期表'[日期], DT2, DT1 )
VAR RZ =CALCULATE ( '花名册'[花名册总人数], T )
VAR LZ =CALCULATE ( '花名册'[花名册总人数], T, USERELATIONSHIP ( '花名册'[离职日期], '日期表'[日期] ) ,USERELATIONSHIP('花名册'[@离职部门],'部门'[部门]))
VAR YR =CALCULATE ( '人员异动'[异动总人数], T )
VAR YC =CALCULATE ( '人员异动'[异动总人数], T, USERELATIONSHIP ( '人员异动'[异出部门], '部门'[部门] ) )
RETURN
RZ - LZ- YC + YR
月离职率
月离职率:=VAR T =
VAR DT1 =MAX ( '日期表'[日期] )
VAR DT2 =CALCULATE ( MIN ( '日期表'[日期] ), ALL ( '日期表' ) )
RETURN
DATESBETWEEN ( '日期表'[日期], DT2, DT1 )
VAR T1 =
VAR DT1 =DATEADD ( STARTOFMONTH ( '日期表'[日期] ), -1, DAY )
VAR DT2 =CALCULATE ( MIN ( '日期表'[日期] ), ALL ( '日期表' ) )
RETURN
DATESBETWEEN ( '日期表'[日期], DT2, DT1 )
VAR RZ =CALCULATE ( '花名册'[花名册总人数], T )
VAR LJLZ =CALCULATE ( '花名册'[花名册总人数], T1, USERELATIONSHIP ( '花名册'[离职日期], '日期表'[日期] ),USERELATIONSHIP('花名册'[@离职部门],'部门'[部门]) )
VAR LZ =CALCULATE ( '花名册'[花名册总人数], USERELATIONSHIP ( '花名册'[离职日期], '日期表'[日期] ),USERELATIONSHIP('花名册'[@离职部门],'部门'[部门]) )
VAR YR =CALCULATE ( '人员异动'[异动总人数], T )
VAR YC =CALCULATE ( '人员异动'[异动总人数], T, USERELATIONSHIP ( '人员异动'[异出部门], '部门'[部门] ) )
RETURN
DIVIDE ( LZ, RZ - LJLZ - YC + YR )
4、最终结果
切片9月
切片12月
by焦棚子
请点击【立即购买】或者【升级VIP】获得案例附件。
评论