DAX+M 语言应用场景：如何得到含有全国维度的图表？

小编最近遇到一个问题，就是要得到下图所示效果，全国这个值柱形图对应的是所有地区销售额的平均值，其他地区对应的是各自地区的销售额：

我们都知道全国平均销售额和各地区的销售额计算是不同的公式，那么， 怎么将他们结合起来放到一个维度里面并展示出不同的结果呢？

1 解题思路

其实要想得到这样的结果，首先要构造出这样一个维度，然后分别计算出全国平均销售额和各地区销售额，然后将这两个公式跟构造出的维度结合起来计算就应该可以得到这样的效果了。

首先我们来看下如何构造这样的维度，我们的模型中已有一个省份的维度表，其中是有各个地区的维度的，缺少的就是"全国"这一个维度，那么我们可以选择直接在维度表中手工添加，也可以使用Power Query去构造，另外也可以使用DAX构造表的函数去构造出这样一张表。

手工添加大家肯定都会添加，那么接下来我们来看一下分别使用DAX函数和M函数如何构造出以下这样一张表：

2 使用DAX构造新的维度表

DAX中常见的返回表的函数有SUMMERIZE、ALL、DISTINCT、VALUES，我们来看下这几种函数结果：

我们可以看到ALL、VALUES都会有一个空行出现，这里就是DISTINCT和VALUES函数的区别：

1、 DISTINCT和VALUES函数的区别在于对于空白行的处理上，VALUES包括没有匹配的空白行，但是DISTINCT不返回没有匹配的空白行。
2、另外的区别是DISTINCT函数允许列名或任何有效的表达式作为其参数，但VALUES函数仅接受列名或表名作为参数。

因为ALL和VALUES都会有一个空行出现，不是我们想要的效果，所以我们采用DISTINCT来构造表。
现在我们有了关于地区的不重复值，那么怎么增加全国维度呢？其实就是需要再增加全国这一行数据，提到行就应该想到ROW函数，这个函数可以构造一行值：

那么我们生成了两张表，如何将两张表合并呢，这时就用到了UNION函数，结果如下：

目前为止，我们得到了含全国维度的地区这一列值，那么怎么得到它的地区Id呢？
如果要得到地区和地区Id两列值的表，我们可以想到使用SELECTCOLUMNS函数，这个函数可以返回你选择的多列值，我们来试一下：

由于省份表中每个地区为多行，这时我们可以再使用DISTINCT去重，得到如下结果：

那么接下来我们结合起来这几个函数就可以得到想要构造的表了，最终效果如下：

3 使用M构造新的维度表

同样的，使用M构造表的思路是一样的，首先选择省份表中地区和地区Id两列值，然后去重；再构造一张只有“全国”一行的表，然后追加就可以了。
首先我们先了解下构造及引用列、记录和表的语法：

构建列的公式={列的内容}，用{ }代表列
构建记录的公式=[标题=内容]，用[ ]代表记录
构建表的公式=#table({标题},{{每列的内容}} )

引用表：=表名称
引用列：=表名称[标题]
引用记录：=表名称{行号}
引用数据：=表名称[标题]{行号}

了解以上语法，我们再来看下最终构造表的M代码：

最终结果如下：

4 如何求全国平均销售额

表构造好了之后，我们就要求全国平均销售额了，那么首先要求出来全国销售额及地区数量，最后两者相除就可以得到全国平均销售额了。
我们先看，如何求全国销售额？

有的小伙伴会想，sum直接求得的销售额不就是全国销售额吗？如果不给这个销售额任何上下文环境的话，这么理解是没有问题的，但是DAX的不同之处就在于此，虽然sum的结果是整列销售额的合计，但是这个合计会跟随不同的计值上下文的变化而变化的，就是说你给它不同的上下文得到的结果是不一样的。

如下图所示：

这时就需要想到如果想不让销售额的合计随着外部筛选上下文的改变而改变，就得想办法取消其筛选，这里就用到了之前提到的ALL函数。

ALL函数是一个返回表的函数，它返回表中的所有行或者返回列中的所有值，同时忽略可能已应用的任何筛选器。

我们来看下修改后的结果：

接下来我们该计算地区的数量了，我们可以直接对地区表计数然后减1，也可以对省份表的地区列进行非重复计数。
公式如下：

这里可以看到，两个公式计算出来的结果都不是想要的结果，这跟以上计算全国销售额是一样的道理，需要使用ALL函数取消外部筛选上下文的影响，公式如下：

那么接下来求全国平均销售额就是将两者相除就可以了，公式如下：

5 最终效果

以上，我们得到了一张含有全国维度的表，以及得到了全国平均销售额，那么接下来我们该如何计算最终结果呢？
在之前的文章《DAX语言：听说你想用度量值做切片器？》中有介绍到selectvalue的用法，其实此处与其类似，也可以使用selectvalue，公式如下：

我们还可以使用VAR函数来简化这几个计算过程，公式如下：

小结

DAX相关：

1、 使用ALL函数清除外部筛选上下文；
2、DISTINCT和VALUES均为返回表的函数，但结果不同；
3、DISTINCTCOUNT计算一个数字列中不同单元的数目，即非重复计数；
4、ROW函数可以构造具有单一行的表；
5、UNION函数返回两个或多个表合并之后的结果；
6、SELECTCOLUMNS返回从表中选择的列及DAX表达式指定的新列的表；
7、SELECTVALUE在指定的列中只有一个值时返回该值，否则返回替代结果。

M相关：

1、构建列的公式={列的内容}，用{ }代表列
2、 建记录的公式=[标题=内容]，用[ ]代表记录
3、构建表的公式=#table({标题},{{每列的内容}} )
4、引用表：=表名称
5、引用列：=表名称[标题]
6、引用记录：=表名称{行号}
7、引用数据：=表名称[标题]{行号}

* PowerPivot工坊原创文章，转载请注明出处！

如果您想深入学习微软Power BI，欢迎登录网易云课堂试听学习我们的“从Excel到Power BI数据分析可视化”系列课程。或者关注我们的公众号（PowerPivot工坊）后猛戳”在线学习”。

长按下方二维码关注“Power Pivot工坊”获取更多微软Power BI、PowerPivot相关文章、资讯，欢迎小伙伴儿们转发分享~

Power Pivot工坊