PowerBI 打造全動態最強超級矩陣

2019 年 10 月 6 日
筆記

如果問 PowerBI 可以製作的最強大圖表是什麼，那這個就是了。

作為 2018年的終結篇並同時開啟 2019，Excel120 將以此篇揭示 PowerBI 可以做出的最強大圖表以及固定套路。

先來看看最終效果：

請放大後仔細觀察本圖一分鐘再繼續閱讀，該圖包含巨量PowerBI深度知識以及多重技巧。從PowerBI入門到完整理解本圖表所經歷的知識點包括：

什麼是PowerBI 表格圖表，準確講可以叫匯總表。
什麼是PowerBI 矩陣圖表。
PowerBI 表格，矩陣與 Excel 透視表有何異同。
到底什麼是透視表。
PowerBI 表格與矩陣有何異同。
全動態超級複雜自定義表頭（列）。
全動態超級複雜自定義錶行。
全動態超級複雜自定義值計算。
全動態超級複雜自定義格式。
全動態超級複雜自定義顏色。
小計行的處理。
總計行的處理。
順序的處理。

這其中涉及大量PowerBI原理以及DAX知識，以本圖表為例，我們來看看它到底有多複雜：

千萬不要妄想拖拽出來，這個圖表從頭到尾，全部都是用純DAX寫出來的。我還記得很多夥伴截了個圖就在群里問，說PowerBI可以做出XXX圖嗎？回答：可以的。接着問：請問怎麼做。沒有回答了。本文就是徹底回答。

大部分人根本不理解透視表

從現實經驗來看，很多人只是在用透視表，實際情況是幾乎 99% 的人根本不知道到底什麼是透視表。

與此類似的概念還包括：交叉表，樞紐表，中國式報表，它們全都是一回事嗎，抑或有哪些區別，以及知道這些概念到底有沒有用。

限於篇幅以及主題相關性，這裡拋出引起思考，在未來不久將推出的《Excel & Power BI 進化論》課程中將徹底講透這些問題，如果你看完本文，被該圖表所震撼，但即使是拿到案例文件都無法照着做出來的話，那你需要打碎認知，屆時重新來學習。

因為，這並不是一個簡單的問題，如果你打開微軟Excel來觀察這個描述，它是這樣寫的：

這裡僅僅是透視表具有的功能，卻並沒說清楚什麼是透視表。當然，我們也不在這裡糾結於概念。

PowerBI 的圖表是如何被展示的

很多初學者都會好奇一個問題，那就是：PowerBI 圖表背後是什麼？並且經常犯一個認知錯誤，那就是：把創建好的一個度量值拖拽到圖表的軸上，並發現無法成功。

PowerBI 是默認不支持將度量值作為觀察的角度的，這與很多其他BI軟件是不同的。

PowerBI 任何圖表的背後，都是一個由 DAX 查詢完成的小表，所有的圖表都是基於這個小表來進行展示。而其中最複雜的一個圖表就是矩陣。

為了循序漸進地真正理解矩陣，我們先要理解一個成為表格的圖表。任何一個圖形類圖表都可以轉化為表格，例如：條形圖，餅圖，瀑布圖等，無一例外，都可以轉換為表格，或者更本質地說：要想做圖，先做表。

PowerBI 中作圖通用套路

要想做圖，先做表，而很多初學者甚至是熟練者都沒有形成這個習慣。很多人無法用PowerBI原生圖形做出想要的圖，其重要原因之一就是根本沒想好這個圖到底是怎麼構成的。

如何想清楚一個圖到底是怎樣構成的？如果你可以想出來這個圖的表，那麼，就一定可以想出這個圖，進而做出這個圖。

PowerBI 中的表格圖表

我們來看看，你是否理解這樣的一個表格：

這個表格的不尋常之處在於：似乎存在很多重複，而且列排布的順序不是很好。但這就是表格。在PowerBI的表格圖表準確來講是一個分組匯總表。注意：

它可以只分組，無匯總。
它可以只匯總，無分組。
它可以既有分組，又有匯總。

凡是文本類型的字段（列），只能用來分組；而數字類型的字段（列），但拖拽進來時，就有不穩定的表現。例如：年齡是一個數字，但通常只會用來分組，不會把年齡加起來。

將這個表格圖表換成一個圖形類圖表，例如：

不難感受表格與圖表之間的轉換相當自然。相對於圖形類圖表，表格其實可以一次性展示更多信息。

這裡需要注意的是，表格從結構上分為：

表頭行
表元素行
總計行

在PowerBI中製作任何圖表，幾乎都可以考慮該圖表的分組匯總表結構。如果熟悉SQL的戰友，可以直接理解一個分組匯總表就是一條SQL查詢結果。

表格圖表的本質

PowerBI中，所有圖形類圖表，都可以表示為分組匯總表（表格），而分組匯總表的本質等價於一條SQL語句。SQL語句是對數據庫的查詢，它分成5個階段：

選擇基礎表，如：產品表，訂單表，地點表，日期表。
建立關係，如：左外連接或笛卡兒積等。
選擇列
分組
組內匯總返回這個查詢結果。

PowerBI 表格的本質是由 DAX 完成上述等價的 SQL 的5個步驟。而且更加簡單。在 PowerBI 中，由於已經存在數據模型，數據模型是一個天然的已經建立了關係的表結構，因此，一個經典的DAX查詢，基本是從第三步進行： ADDCOLUMNS( SUMMARIZE( 模型表 , 用來分組的列 ) , 「聚合語義名」 , [度量值] )

其中，SUMMARIZE 完成等價於 SQL 的第三步及第四部，選擇列及分組。而 ADDCOLUMNS 實現匯總。

從表格到矩陣

矩陣，是唯一無法用一個圖表直接表示的結構，要計算出一個矩陣，PowerBI在後台會進行2~3次分組匯總表查詢，最終再拼接形成矩陣。

我們先來形象的理解，什麼是個矩陣呢？

很多戰友會發現矩陣有很多設置，而很少真正理解這些設置之間的關係，為此，如上圖所示，這裡專門展示了一個含有這些差異的形態。

綠色，表示列標題部分。
紫色，表示行標題部分。
藍色，表示數據值部分。
上述三部分由黑色框線區分開來。
淺黃色，行列小計部分。
深黃色，總計行部分。

請動手自己調配出形如這裡的矩陣來進行理解。因此，矩陣需要至少知道四方面信息：

行標題部分，可以有層級。
列標題部分，可以有層級。
值部分，可以有多個值。
匯總部分，可以有層級。

通常，用手可以拖拽出來的矩陣，一定在上述四個部分是存在規律的，系統才能默認的自動計算。一般來說：

行標題是分組。
列標題是分組。
值部分是多種匯總。
匯總部分按值部分的計算進行。

中國式複雜報表

或許中國的領導更不同，他們喜歡看一種幾乎純定製化的沒有構成規律的報告，所以非常複雜，這些領導也都非常聰明，只有他們才能看懂。例如本例：

這在Excel中完全是一個格子一個格子算的，所以Excel是神器，但是PowerBI顯然沒有Excel靈活，但本文做到在PowerBI下可以做到的極致。

如果無法默認存在規律，我們就需要單獨考慮標題列，標題行，值，匯總的分別計算模式：

但總的來所，行列交叉處進行度量值計算。將矩陣叫做交叉表未嘗不可，因為從字面意思可以看出行列交叉處產生運算。

在 PowerBI 中的難度在於，需要綜合考慮格式，顏色，匯總等，導致它成了一件很複雜的事。

複雜矩陣製作套路

現在可以來說明這種幾乎沒有規律的超級複雜矩陣的製作套路了，根據之前的分析，這個套路分成三個階段：

動態計算階段：標題，行，值，匯總的計算。
格式設置階段：值格式，文字顏色等。
特例調整階段：特別單元格的處理等。

以往PowerBI矩陣無法實現複雜效果，就是因為這三個階段無法同時完成好。而在2018年整個一年的優化中，PowerBI目前已經基本具備這種能力。

複雜矩陣製作第一階段：動態計算階段

構造標題列，本例中，使用 DAX 動態構造出標題列：

該標題列的特性在於：

標題是可以動態自動變化的，例如 2019 年並不是靜態文本，而是動態計算的，未來會隨時間而變。
考慮按列排序，才能在矩陣表現時，有希望的排布順序。

構造標題行，本例中，使用 DAX 動態構造出標題行：

本例中，故意做了小計行和總計行以展示處理它們的能力。

構造計算，本例中，使用 DAX 動態完成在行列交叉處的計算：

這是最核心的步驟，這裡採用Excel120此前提出的非侵入式設計模式，動態計算出行列交叉處的值。以示例文件為準。

複雜矩陣製作第二階段：格式設置階段

由於這裡只能使用一個度量值，你會立馬好奇：一個度量值怎麼智能地知道該顯示文本，數字，還是百分比呢？答案是當然不可能。

在本例中，已經將可能地變化做到了極致。首先，這個度量值是變體類型，如下：

數據類型顯示：變體。這是什麼鬼，不知道很正常，99%的人都不知道。如果度量值中使用IF或SWITCH，有可能返回不同的結果，而不同的結果是不同的數據類型，PowerBI 為了支持這種可能的存在，因此就有了變體數據類型，它在此處幾乎起到了決定性的作用：首先，嘗試過就知道一個數字是無法同時顯示數字又在合適的時機顯示百分比的，這是做不到的，因此就無法同時以符合用戶習慣的方式顯示銷售額和增長率，這是無法接受的，因此，這裡全部用文本來顯示。

但是問題來了，如果顯示的全是文本的話，那如何排序，如何為文本設置顏色，文本無法按照數字比較大小啊。

這裡使用了變體數據類型，讓這裡的度量值返回值作為默認結果，而內部計算可能是文本，用這個方式來解決排序的問題，如下：