PowerBI HR組織架構(gòu)層級通用計算模型
在企業(yè)組織中，涉及到非常多和組織架構(gòu)層級有關(guān)的計算，本文給出通用計算模型。
這種模型具有兩個特點：
表的遞歸關(guān)系
遞歸關(guān)系展開
先來看一個示例。
表示一個公司
一般用如下結(jié)構(gòu)表示一個公司：
公司的結(jié)構(gòu)本質(zhì)是遞歸的，也就是在一個表中，除了當(dāng)前節(jié)點，還要指出它的上級節(jié)點。
表示所有員工
一般用如下結(jié)構(gòu)表示員工以及某種KPI：
現(xiàn)在的問題是如何根據(jù)各個員工的部門歸屬來計算不同部門以及父級部分的KPI。
效果展示
首先給出效果上的展示，如下：
右邊給出兩種可能的展示效果：
部門匯總型
給出直接管理人員的KPI
給出包括子級所有人員的KPI
人員獨立型
給出不同人員的單獨KPI
層級展開的實現(xiàn)方法
表的遞歸關(guān)系在表示組織結(jié)構(gòu)的時候非常有效，但是我們往往需要它的展開型結(jié)構(gòu)，這就需要對遞歸進(jìn)行展開。
PowerBI 的 DAX 非常強(qiáng)大，給了相關(guān)函數(shù)可以直接使用，非常輕松。
第一步，建立計算列，得到遞歸的展開路徑，用 PowerBI DAX 實現(xiàn)，如下：
Path = PATH( tbOrg[ID] , tbOrg[PID] )結(jié)果如下：
注意：Path 不是度量值，是計算列。
這樣，就知道對于任何一個 Item，它在層級中的實際位置。
第二步，對任何一個 Item，建立計算列，表示層級。用 PowerBI DAX 實現(xiàn)，如下：
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 ) )注意：有學(xué)習(xí)了BI佐羅 DAX 基礎(chǔ)的伙伴會看出來，L1，L2，L3，L4 不帶 Name 的命名，是可以作為按列排序的排序列的，而帶有 Name 命名的列才是名稱列。因此，對于一個層級就需要建立一對計算列，表示排序和名稱。
本案例分別建立了 8 個計算列，效果如下：
注意：以上內(nèi)容全部由計算列構(gòu)成。
問題來了：
我們怎么知道要展開到多少級呢？
展開一級就創(chuàng)建兩個計算列，那有10個層級，難道要創(chuàng)建20個計算列嗎？
對于遞歸的層級問題，理論上可以展開很多，但考慮到真實的業(yè)務(wù)環(huán)境，一個企業(yè)或某種系統(tǒng)，一般不會有這么多層級，不然是很難管理的，這就保證了實際當(dāng)中，這種展開往往達(dá)到四個層級就比較多了。這也就確保了即使有一定的手工工作量來創(chuàng)建計算列也是可以接受的，這樣就完成了層級的展開。
小結(jié)：
要實現(xiàn)遞歸表的層級展開，總共分兩步：
創(chuàng)建計算列，用 PATH 函數(shù)計算展開路徑
創(chuàng)建計算列，根據(jù)展開路徑展開
實現(xiàn)層級計算
以本例為例，要實現(xiàn)層級計算。
第一步，建立數(shù)據(jù)模型：
第二步，創(chuàng)建度量值：
用 PowerBI DAX 實現(xiàn)，如下：
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()
)其中，[KPI.Core] 計算了員工的 KPI。而 [KPI.Scope] 則計算了帶有不同層級的總 KPI。
這樣就滿足了一開始的需求：
如果要看員工層面的 KPI 詳細(xì)表現(xiàn)，可以使用第一種模式。
如果要看部門層面的 KPI 匯總表現(xiàn)，可以使用第二種模式。
第一種模式：
第二種模式：
總結(jié)
本文給出了 HR 組織架構(gòu)層級通用計算模型，與傳統(tǒng)講解 DAX 的理論不同，這里給出了三個重要套路：
定義自遞歸表以及在 PBI 中用兩步實現(xiàn)層級展開
將 Org 和 User 分開并建立數(shù)據(jù)模型實現(xiàn)數(shù)據(jù)結(jié)構(gòu)
按人員和部門給出兩種側(cè)重點不同的表現(xiàn)形式和計算
因此，整套結(jié)構(gòu)的說明充分完備，也具有通用性和擴(kuò)展性。
KPI 在實際中可以是：工資，遲到次數(shù)，績效等；而 [KPI.Core] 的計算可以是加總或者是求平均。
HR 小姐姐不但可以迅速整理組織結(jié)構(gòu)，還可以對其進(jìn)行任性的計算，可謂 HR 出差必備良藥。
在訂閱了BI佐羅講授的《BI進(jìn)行時》課程區(qū)，除了可以下載本文案例，還可以觀看視頻講解。
讓數(shù)據(jù)真正成為你的力量
Create value?through?simple and?easy?with fun?by PowerBI
Excel BI?|?DAX Pro?|?DAX?權(quán)威指南?|?線下VIP學(xué)習(xí)
掃碼與PBI精英一起學(xué)習(xí)，驗證碼：data2020
PowerBI MVP 帶你正確而高效地學(xué)習(xí) PowerBI
點擊“閱讀原文”，即刻開始
↙