使用切片器實現動態移動平均計算

先看完成效果:

【關於移動平均】

我們在看一組數據的趨勢時,通常會使用到移動平均(Moving Average)。移動平均是一段時間內數據的平均值。以國際原油價格為例,我們取過去50天的平均值,也就是過去50天原油價格的總和除以50。到了明天,這組數據又向後推移一天,即從明天往前推移50天的平均值。把每天的移動平均值做成折線圖就是移動平均線,能讓我們的數據趨勢變得平滑。見下圖:

圖中綠色線是國際原油價格的曲線,紅色線是過去50天的移動平均線。移動平均線更加的平滑,更容易讓讀者快速的識別數據變化的趨勢。

【計算靜態移動平均】

我們準備了過去3年的國際原油價格表:

大家注意一點,這個價格不是每天都有的,比如在2014年1月4日和5日就沒有數據。所以我們的計算不能簡單用日期減去50天,需要對日期先進行一個編號處理:

這樣我們就得到了連續的日期編號

*如果日期是連續的則不需要通過編號處理,直接用日期列即可。

接下來我們依據這個編號計算過去50天的平均值:

此例里我們計算了過去50天的移動平均值,接下來我們希望向模型添加一張參數表。實現由用戶點擊切片器來控制計算移動平均的天數。

【動態移動平均】

要想實現切片器的動態控制,我們需要把移動平均值寫成度量值,而不是之前例子里寫到計算列里,因為計算列里的數值不能根據切片器的選擇而變化。

大概的思路,通過HASONEVALUE和VALUES來調動參數表的數值放到公式里。

我們先準備一張參數表並導入Power Pivot模型中

表格預置了10,30,50 三個數值,由用戶選擇是看過去多少天。這些數值可根據實際情況調整。

接下來是動態移動平均的公式:

我們生成數據透視圖看看效果:

【30天的移動平均】

【50天的移動平均】

移動平均值的展現可以根據用戶的切片器選擇而發生變化。時間越長移動平均的曲線越平滑。

這就是我們今天的分享,更多PowerPivot和Power BI內容請關注我們在網易雲課堂的視頻課程「從Excel到Power BI數據分析可視化」系列課程。

*PowerPivot工坊原創文章,歡迎轉載,請註明出處

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

延伸閱讀:

在Power Pivot中使用DATEDIFF函數計算日期時間間隔

使用USERELATIONSHIP實現兩表之間多個關係的調用

在Power Pivot中計算父行匯總百分比

圖解Earlier函數

Power Pivot時間智能之YTD(年初至今累計)

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

當Power BI遇上歐冠決賽

使用切片器實現報告的參數控制

使用切片器實現報告的交互排序

亮瞎雙眼的Power BI可視化圖表

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

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


推薦閱讀:

【Excel技巧】- 辦公常用的十大函數@IF函數
【Power Pivot技巧】使用安全除法函數DIVIDE避免除數為0的報錯
如何快速刪除excel中的所有空行或空列?
當?Power?BI?遇上洪災
[E1-08]單元格合併

TAG:PowerBI | MicrosoftExcel | 数据分析 |