Excel财务小技巧之27「Power Pivot之DAX函数基础」

上期我们完成了数据模型的搭建：销售事实表与门店表、产品表、店员表等建立了关系，实现了跨表透视。

但是，基于我们现有学习：透视表只能拖拽已有的列做简单聚合（求和、计数、平均值），毛利率、YTD、同比这些财务分析的核心指标，没法直接拖出来。

毛利率需要（收入 - 成本）/ 收入，YTD需要从1月1日到当前日期的累计，这些都不是现成的列，而是动态计算规则。

本期开始我们就来解决这个问题：用 DAX 写出度量值

一、DAX和度量值

1.什么是DAX？

DAX（Data Analysis Expressions），中文称“数据分析表达式”

是Power Pivot、Power BI 通用的公式语言。它的语法看起来有点像Excel 函数，但计算逻辑完全不同——Excel 函数基于单元格，DAX 基于表和列。

DAX 的核心能力是在数据模型中创建动态计算规则，这些规则会随着你筛选条件的变化自动重新计算。

2. 度量值：DAX 最常用的产物

简单来说，就是用DAX函数写度量值。

度量值是用 DAX 写的计算规则，它不存储在表中，而是在你把它拖到透视表的值区域时实时计算。它会自动响应切片器、行标签、列标签的变化。

举个例子：你写了一个度量值：销售数量 = SUM(销售数据完整版1月[销量])。当你把品牌“小米”拖到切片器时，这个度量值自动只汇总小米的销售数量。你不需要写任何 IF 或 SUMIFS。

3. 如何创建、编辑、删除度量值？

（1）创建度量值

Step0:养成习惯：单独建一张度量值表来存放度量值

进入Power Query→主页→输入数据

在创建表界面重命名为“度量值表”

结果如下：

关闭并上载至数据模型

Step1：进入新建度量值界面

进入Power Pivot→度量值→新建度量值

Step2：创建度量值—以销售数量为例

首先选择表名（即：度量值所在表），此处选择度量值表

度量值名称：自定义，输入销售数量

如果有必要，可以添加说明

在公式区域先输入：

=sum(

会自动带出所有的字段

下拉之后选择销量

双击字段，补全公式括号，再点击确定

（2）编辑、删除度量值

编辑度量值：

进入Power Pivot→度量值→管理度量值，点击编辑

删除度量值：

进入Power Pivot→度量值→管理度量值，点击删除

4.度量值VS计算列VS自定义列

（1）如何操作计算列？

【操作步骤】

进入Power Pivot→设计选项卡→点击添加→在编辑栏输入公式，如：

='销售数据完整版1月'[实销单价]*'销售数据完整版1月'[销量]

【注意】此处的公式，只要是来自模型的字段，输入英文状态下引号，会自动带出字段列表，点击选择即可

结果如下：

（2）度量值VS计算列VS自定义列

自定义列（Power Query）：清洗数据时，按行加工新字段。

计算列（Power Pivot）：建模时，按行加工新字段

度量值（Power Pivot）：建模时，写动态规则，不占空间，随筛选变化。

【总结】能用度量值，就不用自定义列和计算列

二、财务分析常用度量值基础

本文依旧延续之前的手机销售案例，来分享财务分析常用的度量值使用

1.聚合类（SUM/COUNT等）

（1）SUM：只能用于单列求和

=sum('销售数据完整版1月'[销量])

（2）COUNT：用于单列计数（非空单元格）

=count('销售数据完整版1月'[订单号])

（3）其他聚合函数

包括：AVERAGE/MAX/MIN等

（4）聚合函数使用注意事项

一是COUNT 只数非空单元格：如果想统计“所有行数”（包括空值），用 COUNTROWS(表)。

二是AVERAGE 自动忽略空值：如果想把空值当作0来平均，需要先替换空值，或者用 SUM(金额) / COUNTROWS(表)。

三是MAX/MIN 对文本也有效：如果用于文本列，MAX 会按字母顺序返回最后一个，MIN 返回第一个（财务不常用）

四是在 DAX 中，SUM、AVERAGE 等聚合函数天生就会响应透视表的行、列、切片器筛选，不需要你手动加条件。这与 Excel 中的 SUM（不响应筛选）完全不同。

（5）透视表举例

把刚刚写的两个度量值放在透视表的值选项，生成如下透视表：

【总结】在跨表透视中，筛选/行/列通常来自维度表，值来自度量值（度量值计算自事实表）

（6）给透视表加切片器

添加方式：单击透视表任一单元格→数据透视表分析→插入切片器→选择字段→点击确定

可以实现自动筛选

如何调整切片器与透视表的连接？实现切片器只控制部分透视表？

单击切片器→点击报表连接→选择对应的数据透视表

2.毛利率（SUMX和DIVIDE）

上期我们只能在透视表旁边手动写 Excel 公式算毛利率，一旦筛选变化，旁边的公式不会跟着变。现在用度量值解决。

（1）具体写法

Step1：写出总成本度量值

=sum('销售数据完整版1月'[加权采购成本])

Step2：写出“总收入”度量值

思路：先计算出含税销售额，再用含税销售额/1.13得到总收入

SUMX用法：

SUMX 是 DAX 中的迭代聚合函数，用于对表中每一行计算表达式，再将所有行的结果求和。

比如：对每一行求：单价*销量，再对上述计算结果进行求和

语法：

SUMX(表, 表达式)

总收入计算：

=SUMX('销售数据完整版1月','销售数据完整版1月'[实销单价]*'销售数据完整版1月'[销量])

='度量值表'[实销总金额]/1.13

Step3：写出“毛利率”度量值

=1-DIVIDE('度量值表'[总成本],'度量值表'[总收入])

DIVIDE 函数：安全除法，分母为零时返回空值（不会报错）。

（2）透视结果

【本期总结】

本期我们介绍了DAX和度量值的基础知识，介绍了财务分析常用的基础度量值

【下期预告】

下期我们介绍日期表、FILTER/ALL/CALCULATE等DAX函数，并进行可视化实操演练。