Power Pivot聚合函數與Calculate函數詳解

本文由Stephen在PowerBI大師微信公眾號原創發布

發布學習DAX的確比可視化和獲取數據要多花一些氣力，不過有了上個章節的對DAX原理的鋪墊，第四章的內容可謂溫故而知新。把原理和新公式相結合，就好像你剛開始學Excel公式Sum,If,Vlookup...，掌握其實並不難，一個新公式+一次實踐練習就可以解鎖一個新技能。當你完成整個第四章 24個公式的學習時，你就好比掌握了太極拳的24個精髓招式，組合起來運用自如可以不變應萬變。

1 最常用的公式

DAX函數部分是與Excel函數是相通的，對於基本的時間日期、信息、邏輯、數學、統計、和文本函數用法大致相同，這部分基礎類函數我假設大家有一定的認識，百度也能夠搜到Excel公式海量的解釋和案例，你可以按需補充，現學現賣，在這裡不多講。

我想把時間專註在DAX的核心類系，聚合、篩選、時間智能、關係、迭代等等，為了更好地把這些技術一個個解鎖，我選取了24個最常用的函數，並把這些函數的學習細分成了3個階段。第一階是最常用最核心的部分，攻克了它便可以勝任一些小的數據分析模型；第二階較為容易，與Excel的函數很像，可以說是Excel版的擴展；第三階相對前兩個階段來講屬於高階函數，然而我們有了前兩階的學習基礎，它不過是另一個小山頭。掌握了這24個公式，我相信它們至少可以覆蓋到80%的數據分析需求，對於另外的20%我們可以現用現查。

2 聚合函數

在度量值公式欄里，無論你輸入還是[，智能提示的都不會是列，而是其他已創建好的度量值，所以度量值是不可以直接引用列的。比方咖啡數據表中的[數量]列, 該表有近3萬行，把這3萬行的數據放入度量值中是沒有任何意義的。度量值輸出的是一個計算結果，所以我們只有用聚合函數求計算列的聚合值才有效。

聚合函數與Excel的基本計算函數是非常相像的，區別是Excel引用的是單元格，而DAX引用的是列。我們仍在咖啡數據表的基礎上來完成:

<公式1>求咖啡賣的杯數 [銷售量]=Sum(咖啡數據[數量])；

<公式2>求[平均杯數]=Average(咖啡數據[數量])；注意公式欄里的解釋也是非常有用的提示。

<公式3>如果每一行數據視為一位顧客購買的訂單，求訂單中最大的杯數[最大杯數]=Max(咖啡數據[數量])；

<公式4>求訂單的數量[訂單數]=Countrows(咖啡數據)，這裡你發現，聚合函數不僅可以引用列還可以引用表，Countrows即計算表或列的行數。

<公式5>求我們在全國有多少個城市分店[城市數量]=distinctcount(咖啡數據[城市]), Distinctcount是計算列中不重複值的數量。

上面5個函數的計算結果如下表。同理，計算最小值用Min，計算空白項個數Countblank，還有Count，CountA(計算列中不為空的數目)等等。這些道理是一模一樣，大家自行挖掘，也可以隨用隨查。

3 Calculate 最強大的函數

從Calculate的語法結構我們可以看出它能夠把計算表達式和篩選條件整合起來。我們前面把度量值比作帶著漏斗的計算器，那麼<公式6>Calculate就是漏斗與計算器之間的啟動鍵，它能夠賦予漏斗按指定的條件來執行過濾篩選，同時讓計算器執行運算。

我們按上面製作一個矩陣表，行為原材料[咖啡種類]，列為杯型[體積]，值為[銷售量]。現在我們再做一個試驗，再創建一個[銷售量2]=Calculate([銷售量], 原材料[咖啡種類]="拿鐵", 杯型[體積]="480ml")，把矩陣表中的值替換為[銷售量2]。你會得到下面這個表，是不是有些驚訝！難道是出Bug了？所有的值都為在拿鐵咖啡和杯型體積=480ml限定條件下的銷售量。

這個輸出的結果是完全沒有問題的，我們做這個例子是為了說明Calculate工作原理，從這個結果中可以得出幾點關於Calculate函數重要的結論:

1.完全符合我們在3.4《度量值工作原理》的步驟邏輯，第2步Calculate可對初始篩選增刪改，即生成新的篩選上下文。在例子中，比如左上角第一個格子的初始篩選上下文是卡布奇諾和體積360ml，然而Calculate的篩選條件覆蓋了初始篩選上下文，把它更改成了拿鐵和體積480ml並執行計算。

可以說掌握了Calculate就好比掌握了自定義功能，比方說標準的拿鐵咖啡是由濃縮、牛奶、奶泡按一定比例構成，現在你可以自定義修改配方，加入香草和焦糖變成香草焦糖拿鐵。

2.Calculate可以應用在多個表。注意到[銷售量2]公式引用的是原材料表和杯型表，兩個都是Lookup表。按照我們在3.2《表與表關係》的原理，啟動Calculate彷彿是開啟了漏斗過濾器的一個閥門，使數據像水一樣順流而下，執行對數據表的篩選。

3. 篩選條件拿鐵和體積=480ml兩者同時滿足，是AND關係。在DAX中，如果想表達或的關係，運算符為「 || 」。但是在本例中如果直接寫=Calculate([銷售量],原材料[咖啡種類]="拿鐵"||杯型[體積]="480ml") 這個是無效的。記住在Calculate中使用||時，必須是引用同一列，比方=Calculate([銷售量],杯型[體積]="360ml"||杯型[體積]="480ml")這個是可以的。

4.你可能注意到表中無論是橫向還是縱向，總計都不是分類的加總。這與我們在3.4《度量值工作原理》中的原則完全符合 「B.每一個值都是獨立計算的」，即使是總計。

5. 有人稱Calculate為超級版Sumif，但它的能力遠比Excel中的Sumif強大多了。我們這個例子用的銷售量是Sum求和，然而Calculate可以與各種聚合函數搭配組合Average, Count, Max...可以說是沒有限制。要知道Excel中是沒有Maxif/Minif這種公式的。

6. Calculate的強大還不止於此，它可以與各種篩選器函數搭配組合如All,Values,Filter來發揮更強大的功能，後面的幾節我們分別介紹。

---

aHR0cDovL3dlaXhpbi5xcS5jb20vci8wemhyY3gtRVVrSXRyVnNROTIzQg== (二維碼自動識別)