106_Power Pivot之HR入离调转、在职、离职率相关指标

焦棚子 2020-03-0116:25:34评论3,2912字数 3302阅读11分0秒阅读模式

焦棚子的文章目录

请在文末下载附件

一、背景

之前有帮公司HR做了些员工入离调转、在职、人工成本分析等(体量:4000人左右)。在和其他朋友交流的时候得知,貌似这些指标在Excel中写出来比较臃肿,用表格公式做起来会比较麻烦且时间维度的交互比较难。

二、案例

在上面背景下,今天写个入离调转、在职、离职率这几个简单的指标,至于人工成本 、人员结构等分析后续有好的素材在写,这些模拟数据太麻烦了。闲话不多讲。先看下结果图。

根据月份的选择,结合左边基础数据验证指标

基础数据

1、花名册

106_Power Pivot之HR入离调转、在职、离职率相关指标
1、花名册(模拟数据因为以上指标只需要表中几个关键的字段,实际工作中还有很多字段)

2、人员异动

106_Power Pivot之HR入离调转、在职、离职率相关指标
2、人员异动;注意黄色人员在9月中的体现

3、部门表

106_Power Pivot之HR入离调转、在职、离职率相关指标
3、部门表

4、日期表

106_Power Pivot之HR入离调转、在职、离职率相关指标
日期表

建立关系如下:

106_Power Pivot之HR入离调转、在职、离职率相关指标
特别注意其中实现关系和虚线关系USERELATIONSHIP的使用

三、度量值

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
106_Power Pivot之HR入离调转、在职、离职率相关指标

月离职率

离职率定义有很多种,作如下定义:当月离职人数/当月累计在职人数(包含离职),

月离职率:=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 )
106_Power Pivot之HR入离调转、在职、离职率相关指标

来一个时间选择2018年9月定格的最终结果。

106_Power Pivot之HR入离调转、在职、离职率相关指标
结果

四、总结

1、指标要服务业务,要理解业务;

2、虚线关系userelationship的使用;

USERELATIONSHIP 函数 (DAX)

3、构造特定业务的上下文,这个案例主要是时间类的上下文。

 

五、修正

由于之前没有考虑到异动带来的离职归属到异入部门的问题,特做修正。

1、在花名册中加入字段【@离职部门】的概念;

106_Power Pivot之HR入离调转、在职、离职率相关指标

2、【@离职部门】与部门建立关系

106_Power Pivot之HR入离调转、在职、离职率相关指标

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月

106_Power Pivot之HR入离调转、在职、离职率相关指标

切片12月

106_Power Pivot之HR入离调转、在职、离职率相关指标

 

by焦棚子

 

焦棚子的文章目录

请点击【立即购买】或者【升级VIP】获得案例附件。

隐藏内容需要支付:¥10
立即购买 升级VIP

焦棚子
  • 除非特殊声明,本站文章均为焦棚子原创,转载请务必保留本文链接!
  • 《106_Power Pivot之HR入离调转、在职、离职率相关指标》链接:https://jiaopengzi.com/183.html