有了這幾個公式,你也可以快速搞定累計求和
假設有一組數據,
已知每一個產品貢獻的利潤,如果要計算前幾名產品的貢獻利潤總和,或者每一個產品和利潤更高產品的累計貢獻佔總體的比例,就涉及到累計求和,數據量較小的情況下,在 Excel 中可以輕鬆實現,畢竟 Excel 是對單元格進行操作。
但是在 PowerBI 中,對整個欄位運算很簡單,而對每一行的數據進行篩選匯總,卻需要動一點小心思,下面就以此表為例介紹幾種常用的累計求和方式,並以此案例來進一步掌握幾個函數的用法。
利用 M 函數累計求和
將該數據導入到 PQ 編輯器中,添加自定義列,
公式欄中輸入M函數,
=List.Sum(Table.SelectRows(更改的類型,(x)=>x[利潤]>=[利潤])[利潤])
然後就新增加了一列累計利潤,
對這裡的公式稍微解釋一下,
Table.SelectRows函數用來篩選表中符合條件的行,"更改的類型"是表的名稱,在PQ編輯器中,每一個步驟就是一個表,步驟名就是該表的名稱。
(x)=>x[利潤]>=[利潤]是篩選條件,這個相對比較難理解,還記得之前介紹的自定義函數嗎,見到=>這個符號,就代表這裡有個自定義函數,只是在這裡是內置函數。意思為每一行為變數x,只要第x行的利潤數大於等於當前行的利潤,就利用Table.SelectRows函數把改行篩選出來。
而 Table.SelectRows 函數返回的是一個表,如果要提取該表中利潤列,就是在該函數外面再加上欄位名進行深化,於是就有了Table.SelectRows(更改的類型,(x)=>x[利潤]>=[利潤])[利潤],它返回的是符合條件的利潤列。
最後再在最外面套上List.Sum函數對該列進行求和,就得到了以上表中累計利潤。
利用 DAX 進行累計求和
在 DAX 中可以用 EARLIER 函數和 VAR 定義變數兩種方式。
使用EARLIER 函數
累計利潤2 =
CALCULATE(SUM("產品"[利潤]),
FILTER(ALL("產品"),"產品"[利潤]>=EARLIER("產品"[利潤])))
EARLIER函數以前專門介紹過,可以參考:EARLIER 函數 | PowerBI星球
使用VAR定義變數的方式累計求和
累計利潤3 =VAR cur_profit="產品"[利潤]
RETURNCALCULATE(SUM("產品"[利潤]),
FILTER(ALL("產品"),"產品"[利潤]>=cur_profit))
這兩種方式返回的結果和利用M函數的效果完全一致,
這兩種函數,其實和上面M函數一樣,都是通過各自的方式篩選符合大於等於當前利潤的數據行,然後求和得到結果。
上面的DAX求和用到的是CALCULATE,其實這裡也可以用SUMX函數,
累計利潤4 =SUMX(filter(all("產品"),"產品"[利潤]>=EARLIER("產品"[利潤])),"產品"[利潤])
累計利潤5 = VAR cur_profit="產品"[利潤]
RETURN SUMX(FILTER(ALL("產品"),"產品"[利潤]>=cur_profit),"產品"[利潤])
CALCULATE函數第一個參數是匯總方式,第二個及之後的參數為篩選條件,而SUMX函數第一個參數篩選條件,第二個參數為求和欄位,在求和的計算上,SUMX可以實現和CALCULATE一樣的效果。
案例中的數據已經按照利潤從大到小進行排列,理解上更直觀,其實即使不進行排序,這幾種方式也都可以返回正確的結果,因為計算的邏輯和每一行的順序無關。
上面幾個公式,需要的時候可以隨時套用,當然在理解的基礎上靈活掌握這些函數,可以更有效的進行各種數據分析。累計求和在數據分析中十分常用,比如ABC分析,後面會進一步介紹。
TAG:PowerBI星球 |