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

2018-10-10 13:04:02 ⋅ 6292 ⋅ 9 ⋅ 40

传送门：https://jiaopengzi.com/183.html

一、背景

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

二、案例

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

基础数据
1、花名册
file
模拟数据因为以上指标只需要表中几个关键的字段，实际工作中还有很多字段

2、人员异动
file
注意黄色人员在9月中的体现

3、部门表
file

4、日期表
file

建立关系如下：
file
特别注意其中实现关系和虚线关系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

file

月离职率
离职率定义有很多种，作如下定义：当月离职人数/当月累计在职人数（包含离职）

月离职率:=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 )

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

四、总结

1、指标要服务业务，要理解业务；
2、虚线关系userelationship的使用；USERELATIONSHIP 函数 (DAX)
3、构造特定业务的上下文，这个案例主要是时间类的上下文。

by焦棚子

回复数量: 40

L*
⋅ 2018-10-18 10:55:48

chengzongliao@foxmail.com
感谢分享
焦棚子微软 MVP
⋅ 2018-10-18 11:02:41

已发，请查收。
choicechoi 想成为一名数据分析师，可只懂皮毛
⋅ 2018-10-20 17:35:26

82673220@qq.com,谢谢
焦棚子微软 MVP
⋅ 2018-10-20 20:36:10

@choicechoi
已发，请查收。
Ntt docomo
⋅ 2018-10-22 08:39:57

885645239@qq.com
0086
⋅ 2018-10-26 22:31:41

89340992@QQ.com 感谢分享。
焦棚子微软 MVP
⋅ 2018-10-27 18:26:14

@Ntt docomo
不好意思，才看到，已发送，请查收。
焦棚子微软 MVP
⋅ 2018-10-27 18:26:23

@0086
已发，请查收。
lynn
⋅ 2018-11-08 20:51:37

150385225@qq.com 求附件，谢谢
方便加个Q。。因为我也做过一个DAX计算离职率的表，有点小问题想和你讨论一下，谢谢~！
焦棚子微软 MVP
⋅ 2018-11-09 09:12:32

@lynn
已发送
大风起兮pbi凉
⋅ 2018-11-14 14:15:26

请问楼主，以您的这些指标为基础，做移动平均怎么搞啊？季度平均、年平均啥的，powerbi自身提供的移动平均建立的得出的度量值都是空值
焦棚子微软 MVP
⋅ 2018-11-14 20:51:05

@大风起兮pbi凉

移动平均，对应的季度也好，天数也好，关键在于你当前布局中对应分子分母上下文创建即可。
没有看到你的数据模型，无法回到你具体的问题。
大风起兮pbi凉
⋅ 2018-11-15 16:49:59

@焦棚子
我的有两张表，一张基础档案表，一张日期表，双方没有关联，
我的在职人数这样写：

在职人数 =

VAR t1 = date(year(min('日期表'[Date])),month(min('日期表'[Date])),25)

VAR t2 = date(year(max('日期表'[Date])),month(max('日期表'[Date])),25)

var user_zz = FILTER('基础档案personnel base_info','基础档案personnel base_info'[入职时间]<=t2&&('基础档案personnel base_info'[离职时间]>t2||'基础档案personnel base_info'[离职时间]=BLANK()))

return COUNTROWS(user_zz)

怎样以这个度量值为基础写季度平均呢？

或者就以您上述案例中的数据模型为基础，怎样写移动平均呢？求教。
月照
⋅ 2018-11-15 23:29:59

感谢分享!3452148906@qq.com
焦棚子微软 MVP
⋅ 2018-11-15 23:30:14

@大风起兮pbi凉
你还会上个附件吧，明天有空看下。双十一这两天比较忙。
焦棚子微软 MVP
⋅ 2018-11-15 23:32:00

@月照

已发送，请查收。
大风起兮pbi凉
⋅ 2018-11-16 08:27:53

我现在这么搞：
总人数 = DISTINCTCOUNT('基础档案personnel base_info'[userId])
新进数 =
VAR t1 = date(year(min('日期表'[Date])),month(min('日期表'[Date])),25)
VAR t2 = date(year(max('日期表'[Date])),month(max('日期表'[Date])),25)
var user_zz = FILTER('基础档案personnel base_info','基础档案personnel base_info'[入职时间]>=t1&&'基础档案personnel base_info'[入职时间]<=t2&&('基础档案personnel base_info'[离职时间]>t2||'基础档案personnel base_info'[离职时间]=BLANK()))
return COUNTROWS(user_zz)
期初人数 = CALCULATE('指标集'[期末人数],PREVIOUSMONTH('日期表'[Date]))
期末人数 =
VAR t1 = date(year(min('日期表'[Date])),month(min('日期表'[Date])),25)
VAR t2 = date(year(max('日期表'[Date])),month(max('日期表'[Date])),25)
var user_zz = FILTER('基础档案personnel base_info','基础档案personnel base_info'[入职时间]<=t2&&('基础档案personnel base_info'[离职时间]>t2||'基础档案personnel base_info'[离职时间]=BLANK()))
return COUNTROWS(user_zz)
单位平均人数 = ([期初人数]+[期末人数])/2
流动率 = DIVIDE(([新进数]+[离职人数]),[单位平均人数])
离职人数 =
VAR t1 = date(year(min('日期表'[Date])),month(min('日期表'[Date])),25)
VAR t2 = date(year(max('日期表'[Date])),month(max('日期表'[Date])),25)
var user_zz = FILTER('基础档案personnel base_info',('基础档案personnel base_info'[离职时间]>=t1&&'基础档案personnel base_info'[离职时间]<=t2))
return COUNTROWS(user_zz)
离职率 = DIVIDE([离职人数], [单位平均人数])
焦棚子微软 MVP
⋅ 2018-11-16 22:40:58

@大风起兮pbi凉
附件给出来看，光看公式看不出来效果的。
K
⋅ 2018-11-22 21:44:35

2414556146@qq.com 谢谢分享
焦棚子微软 MVP
⋅ 2018-11-22 22:05:38

@K 已发送。
馒头加花卷 Power BI爱好者，一切为了效率
⋅ 2018-11-28 20:20:08

152749436@qq.com
谢谢分享
焦棚子微软 MVP
⋅ 2018-11-28 22:23:24

@馒头加花卷

已发送，请查收。
gaolin256
⋅ 2018-12-10 11:22:30

125008197@qq.com
谢谢分享！
焦棚子微软 MVP
⋅ 2018-12-10 15:22:37

@gaolin256
已发送，请查收。
光明
⋅ 2019-04-15 21:01:04

292595560@qq.com
谢谢
焦棚子微软 MVP
⋅ 2019-04-15 21:50:43

@光明
已发送。
悦悦
⋅ 2019-05-08 22:59:12

284879121@qq.com
感谢！
焦棚子微软 MVP
⋅ 2019-05-10 19:47:46

@悦悦已发送。
PBeason 天天向上，好好学习！
⋅ 2019-06-22 23:15:11

谢谢，可以麻烦发一份么！
minsen6575@dingtalk.com
焦棚子微软 MVP
⋅ 2019-06-24 08:31:03

@PBeason 已发送。
行者效率狂+工具控
⋅ 2019-06-30 00:46:26

感谢分享，有个疑问，在职人数是不是可以直接用花名册，筛选离职日期为空的表，计数，这样就是离职经纪人人数了吧？
焦棚子微软 MVP
⋅ 2019-07-01 11:20:37

@行者
如果是计算一个总人数的话是可以的，主要是要细化到部门去，还要关联异动，这样就不行了。
看你自己的指标定义。
杨众荔
⋅ 2019-08-19 13:14:28

感谢分享，能否发送：85599385@qq.com
焦棚子微软 MVP
⋅ 2019-08-19 14:54:56

@杨众荔当然可以哈。已发送。
Able
⋅ 2019-09-05 15:36:26

115932297@qq.com
感谢大佬分享
焦棚子微软 MVP
⋅ 2019-09-05 16:56:50

@Able 已发送。
xing 只有EXCEL
⋅ 2019-09-05 19:37:29

谢谢分享，想要一份附件。631712815@QQ.COM
焦棚子微软 MVP
⋅ 2019-09-06 08:44:58

@xing 已发送。
Dot1x 哈撒欸
⋅ 2019-10-06 00:36:41

980469001@qq.com