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 为了支持这种可能的存在，因此就有了变体数据类型，它在此处几乎起到了决定性的作用：首先，尝试过就知道一个数字是无法同时显示数字又在合适的时机显示百分比的，这是做不到的，因此就无法同时以符合用户习惯的方式显示销售额和增长率，这是无法接受的，因此，这里全部用文本来显示。

但是问题来了，如果显示的全是文本的话，那如何排序，如何为文本设置颜色，文本无法按照数字比较大小啊。

这里使用了变体数据类型，让这里的度量值返回值作为默认结果，而内部计算可能是文本，用这个方式来解决排序的问题，如下：