EARLIER函數精細化計算行間差

工坊的小夥伴們大家好: n 今天我們來研究一下EARLIER函數。誠如趙老師所言, EARLIE函數是 Power BI 建模分析的分水n嶺,理解它的作用機制我們建模分析的能力將會得到很大提升。今天我們結合實例來學習一下earlier函n數按照不同規則計算行間差的方法。 n

手上有這樣的一組數據:

我們來分別看公司整體相鄰兩件產品生產時間間隔的分布,各車間產品生產時間間隔的分布,每個車間員工生產兩條產品的時間間隔的分布。

公司整體層面

----------------------------------------------------------------------------------------------

我們的想法是計算相鄰兩條記錄之間的時間差。為了便於理解,我們先把生產時間列按照降序排序,之後添加輔助列,點擊建模欄的新建列,我們為生產時間列添加序號列,

因為是看總體的情況,所以「序號1」列是該行的生產時間在所有記錄中的序號,

接下來我們再用如下公式新建一列:

生產時間1 = SUMX(FILTER(生產記錄表,生產記錄表[序號1]=EARLIER(生產記錄表[序號1])+1),生產記錄表[生產時間])

我們從得到的結果來反向理解一下該表達式的含義,仔細觀察可以發現,『生產時間1』列是原來的『生產時間』列整體向上移動了一行得到的,

公式的邏輯是:對於每個序號來說,去找到原來的表中該序號加一的序號對應的生產時間,也就是生產時間相鄰的前一條記錄,這樣的話我們通過兩列做差即可得到相鄰兩條記錄的時間差了。這裡我推薦大家使用DATEDIFF函數,可以通過修改函數的最後一個參數得到想要的時間粒度。

時間差1 = DATEDIFF(生產記錄表[生產時間1],生產記錄表[生產時間],SECOND)

至此我們得到了一列相鄰兩行的時間差數據,稍後我們通過作圖來分析時間差的分布情況。

公司內各車間層面

----------------------------------------------------------------------------------------------

我們的想法是計算同一個車間的相鄰兩條生產記錄之間的時間差。同樣,我們先給生產時間添加序號,而與上節不同的是,我們是給每個車間的生產時間分別編號,此時我們用的表達式為:

序號2 = RANKX(FILTER(生產記錄表,生產記錄表[車間]=EARLIER(生產記錄表[車間])),生產記錄表[生產時間])

接下來,同樣是新建一列生產時間,每行都是原來的生產時間在對應車間內的前一條記錄的生產時間。表達式為:

生產時間2 = SUMX(FILTER(生產記錄表,生產記錄表[車間]=EARLIER(生產記錄表[車間])&&生產記錄表[序號2]=EARLIER(生產記錄表[序號2])+1),生產記錄表[生產時間])

該表達式相對於『生產時間1』多了一個限制條件,即相同車間的限制。

接下來,同樣使用DATEDIFF函數計算同車間相鄰兩條記錄的時間差:

時間差2 = DATEDIFF(生產記錄表[生產時間2],生產記錄表[生產時間],SECOND)

稍後我們來作圖分析時間差的分布。

每個員工生產兩件產品的時間間隔

----------------------------------------------------------------------------------------------

延續之前的思路,我們新建一列序號列,這次排序的條件是相同車間的相同員工,這裡我們只貼出表達式,相信大家已經有一定的理解了。

序號3 = RANKX(FILTER(生產記錄表,生產記錄表[車間]=EARLIER(生產記錄表[車間])&&生產記錄表[員工編號]=EARLIER(生產記錄表[員工編號])),生產記錄表[生產時間])

接下來同樣是新建一列生產時間,只不過這次的限制條件是相同車間的同一名員工,

生產時間3 = SUMX(FILTER(生產記錄表,生產記錄表[車間]=EARLIER(生產記錄表[車間])&&生產記錄表[員工編號]=EARLIER(生產記錄表[員工編號])&&生產記錄表[序號3]=EARLIER(生產記錄表[序號3])+1),生產記錄表[生產時間])

之後用DATEDIFF函數計算兩列時間的差:

時間差3 = DATEDIFF(生產記錄表[生產時間3],生產記錄表[生產時間],SECOND)

至此我們得到了三列時間差,下面分析時間差的分布情況。我的想法是為時間差分組,感謝@葉婷的方法,我們可以通過如下方式來較為輕鬆的為多列數值分組。

首先,我們建立一個參數表,表中是各組名稱和區間

然後,以『時間差1』列為例:

分組1 = CALCULATE(VALUES(分組參數[區間]),FILTER(分組參數,分組參數[最小值]<=生產記錄表[時間差1]&&分組參數[最大值]>=生產記錄表[時間差1]))

然後根據得到的分組列我們做出如下的條形圖:

經過類似操作我們得到各車間和各員工的生產時間間隔分布條形圖:

嗯吶,今天的分享就到這裡啦,能看到這裡的都是真愛呀,么么噠bye ~

*Power Pivot工坊原創文章,轉載請註明出處。

----------------------------------------------------------------------------------------------

延伸閱讀:

工坊實驗室之CLOSINGBALANCE家族

參數表第三彈—最近N天的實現

工坊實驗室之部分切片的實現

Power BI 也能做這樣的組合圖?

工坊實驗室之失靈的篩選

工坊實驗室之R腳本視覺對象

工坊實驗室之數據標籤的位置

工坊實驗室之桑基圖排序

工坊實驗室之流向圖展現

工坊實驗室之使用瀑布圖

亮瞎雙眼的PowerBI可視化圖表

一張圖看懂微軟Power BI系列組件

一張圖看懂Power BI 架構

當Power BI 遇上洪災

當Power BI 遇上歐冠決賽

當Power BI遇上條形碼

當Power BI遇上Visio

----------------------------------------------------------------------------------------------

如果您想深入學習微軟Power BI,歡迎登錄網易雲課堂試聽學習我們的「從Excel到Power BI數據分析可視化」系列課程。或者關注我們的公眾號(PowerPivot工坊)後猛戳」在線學習」

推薦閱讀:

【常用技巧】選擇性粘貼這二個逆天的技巧你肯定不知道!
2017年主要國家對中國評價
關於Excel列印,你需要知道這些知識點
還在excel里不停地敲空格鍵嗎?你該學習了
Power BI動態獲取每日必應圖片背景

TAG:PowerBI | PowerQuery | MicrosoftExcel |