部落格:www。jiaopengzi。com
一、背景
之前有幫公司HR做了些員工入離調轉、在職、人工成本分析等(體量:4000人左右)。在和其他朋友交流的時候得知,貌似這些指標在Excel中寫出來比較臃腫,用表格公式做起來會比較麻煩且時間維度的互動比較難。
二、案例
在上面背景下,今天寫個入離調轉、在職、離職率這幾個簡單的指標,至於人工成本 、人員結構等分析後續有好的素材在寫,這些模擬資料太麻煩了。閒話不多講。先看下結果圖。
根據月份的選擇,結合左邊基礎資料驗證指標
基礎資料
1、花名冊
1、花名冊(模擬資料因為以上指標只需要表中幾個關鍵的欄位,實際工作中還有很多欄位) 2、人員異動
2、人員異動;注意黃色人員在9月中的體現 3、部門表
3、部門表 4、日期表
日期表 建立關係如下:
特別注意其中實現關係和虛線關係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
月離職率
離職率定義有很多種,作如下定義:當月離職人數/當月累計在職人數(包含離職),
月離職率:=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的使用;
USERELATIONSHIP 函式 (DAX)
3、構造特定業務的上下文,這個案例主要是時間類的上下文。
by焦棚子