desc excel 公式_还在用Excel做数据分析报告吗？用PowerBI试试~全民BI

数据场景

▲效果展示

大家好，之前分享过一篇文章讲述如何用PowerBI制作各国GDP排名的动态图表，很多小伙伴都被PowerBI的魅力所折服，摩拳擦掌准备开始学习PowerBI，今天就跟大家分享PowerBI制作简易仪表板的方法，希望大家在工作中能够学以致用。

简单介绍一下这个仪表板，它对销售数据进行了总体的概况分析，包括以下可视化内容：

① 今年销售总额

② 去年销售总额

③ 销售总额同比增长率

④ 销售总额最高的是哪个季度

⑤ 销售总额最高的是哪个月份

⑥ 销售总额最高的是哪一周

⑦ 销售总额最高的是哪一天

⑧ 销售总额同期分析(折线图)

⑨ 销售总额YTD同期分析(折线图)

⑩ 销售总额环比分析 (组合图)

1

数据导入

我们需要导入3张表格：[01-订单明细]，[02-商品价格]，[03-日历表]，大概需要3个步骤来完成导入工作，我们以导入[01-订单明细]表为例：

① 导入Excel文件：从Excel工作簿导入数据

② 表格重命名 ：选中表格，右键重命名即可。

③ 检查数据类型：我们要重点关注日期格式，数字格式是否正确，如果发现格式不对，需要进行相应的调整。

其它表格导入方法相同，不再赘述。

2

创建关系

数据导入后，我们需要创建关系，按住鼠标左键将一个表中的字段拖动到另一个表中相关的字段上即可，前提是两个字段有相同的内容。具体操作参见动图演示:

3

创建度量值

我们总共需要创建12个度量值，这里只演示第一个度量值的创建方法：

度量值创建之前，我们最好新建一个表，将所有的度量值存放在该表中，这样便于以后的管理和查找。具体方法参见动图演示：

度量值的写法也很有讲究，等号前是度量值的名称，等号后是DAX公式。为了让公式更加易读，我们需要把公式分层来写，按【SHIFT+ENTER】键可以换行，【CTRL+[】向左缩进，【CTRL+]】 向右缩进。

PowerBI 有很好的公式录入提示功能，当我们输入函数的开头字母，即可弹出相关的函数名，按上下键选择，按TAB键选中。输入单引号可以弹出可用的表名及字段。

下面的动图展示了输入第一个度量值的方法：

下面是本案例涉及到的所有度量值DAX公式，大家可以直接复制到PowerBI中，公式显示不全请在代码区域左右滑动即可查看全部公式。

1)销售总额

销售总额 = SUMX( '01-订单明细', '01-订单明细'[数量]* RELATED('02-商品价格'[单价]) )

》》代码显示不全请在代码区域左右滑动

代码解析：

我们用 RELATED 函数将 [02-商品价格] 表中的 [单价] 引用到 [01-订单明细] 表，这就好比我们在 [01-订单明细] 表中添加了一列 [单价]，用 [01-订单明细] 表中的每一行 [数量] * 每一行 [单价]，然后用 SUMX 函数对所有相乘的结果进行相加，我们可以简单的将SUMX函数理解为Excel函数中的SUMPRODUCT，先求每一行的乘积，然后将结果相加。

2)销售总额_本年

销售总额_本年 =  CALCULATE( '度量值'[销售总额], '03-日历表'[年份]=YEAR(TODAY()) )

》》代码显示不全请在代码区域左右滑动

代码解析：

CALCULATE 函数我们在之前讲POWER PIVOT时有涉及过，它也是DAX函数的灵魂，本案例中我们就把它理解超级SUMIF函数，第一参数是我们已经求出的度量值[销售总额]，第二参数是筛选条件，也就是对度量值进行特定条件的筛选。

TODAY() 函数获得当天的日期，用 YEAR() 函数获取日期中的年，也就是2019年，所以筛选条件就是 [03-日历表] 中的年份等于2019。最终获得的结果就是2019年的销售总额。

3)销售总额_去年

销售总额_去年 =  CALCULATE( '度量值'[销售总额], DATEADD('03-日历表'[日期],-1,YEAR) )

》》代码显示不全请在代码区域左右滑动

代码解析：

DATEADD 函数可以帮助我们将日期向前或向后推若干个单位。单位可以是年，月，日。

DATEADD('03-日历表'[日期],-1,YEAR) 的含义就是将日历表中的日期向前推一年，这样就获得了去年的日期，然后结合 CALCULATE 函数对度量值[销售总额]进行筛选，就获得了去年的销售总额。

这部分的DAX函数之前在分享POWER PIVOT时已经做了详细的讲解，这里就不啰嗦了。

4)销售总额_上月

销售总额_上月 =  CALCULATE( '度量值'[销售总额_YTD_本年], DATEADD('03-日历表'[日期],-1,MONTH) )

》》代码显示不全请在代码区域左右滑动

代码解析：这里也是用 DATEADD 函数，将日期向前推一个月，和上面的用法类似，只是第三参数由 YEAR 变成了 MONTH。

5)销售总额_YTD_本年

销售总额_YTD_本年 =  TOTALYTD ( '度量值'[销售总额_本年], '03-日历表'[日期] )

》》代码显示不全请在代码区域左右滑动

代码解析：

我们用 TOTALYTD 函数获取2019年年初累计至今的销售总额，这个函数之前也有专门的讲解，不再赘述。往期教程参见：YTD/QTD/MTD 都是啥玩意？用PowerPivot能算出来吗？|Excel103

友情提示：使用TOTALYTD时间智能函数前要将 [03-日历表] 标记为 日期表。

这一步操作是将时间智能功能 (如 TOTALYTD、PREVIOUSMONTH 和 DATESBETWEEN) 所需的元数据设置为正常工作。当使用这些函数之一运行计算时, Power BI 的公式引擎知道获取所需日期的位置。

如果未设置此属性, 则使用 DAX 时间智能函数的度量值可能不会返回正确的结果。具体步骤参见如下动图演示：

6)销售总额_YTD_去年

销售总额_YTD_去年 =  TOTALYTD ( '度量值'[销售总额_去年], '03-日历表'[日期] )

》》代码显示不全请在代码区域左右滑动

代码解析：公式同上，只是将第一参数度量值更改为 [销售总额_去年]

7)销售总额_YOY%

销售总额_YOY% =  DIVIDE ( '度量值'[销售总额_本年]- '度量值'[销售总额_去年], '度量值'[销售总额_去年] )

》》代码显示不全请在代码区域左右滑动

代码解析：

YOY% =(今年销售总额-去年销售总额)/去年销售总额

这里我们用 DIVIDE 安全除法防止除数为0的情况。往期教程参见：用PowerPivot处理同比环比问题，实在是太简单了！|Excel102

8)销售总额_MOM%

销售总额_MOM% =  DIVIDE ( '度量值'[销售总额_本年]- '度量值'[销售总额_上月], '度量值'[销售总额_上月] )

》》代码显示不全请在代码区域左右滑动

代码解析：

MOM% =(当月销售总额-上月销售总额)/上月销售总额

这里我们用 DIVIDE 安全除法防止除数为0的情况。往期教程参见：用PowerPivot处理同比环比问题，实在是太简单了！|Excel102

9)销售总额_TOP_Q

销售总额_TOP_Q =  CALCULATE( VALUES('03-日历表'[季度]), TOPN( 1, SUMMARIZE ( '03-日历表', '03-日历表'[季度], "汇总", '度量值'[销售总额_本年] ), [汇总], DESC ) )

》》代码显示不全请在代码区域左右滑动

代码解析：

SUMMARIZE 函数的第一个参数是表，第二个参数是列(支持多列)，会返回该列的不重复列表，第三参数是汇总列的列名，第四参数是表达式，这里是对本年的销售总额按照季度进行汇总。

SUMMARIZE 最终得到的表如下图所示：

TOPN 函数是用来获取表格的前N个最大值，第一参数为1，表示获取表格的第一个最大值，第二参数是要提取的表格，第三参数是取值的依据列，第四参数DESC表示降序排序，也就是将[汇总]列按降序排序，提取最大值。最后结果如下图所示：

VALUES 函数可以获取指定列的非重复值，结果也是一个表格，如下图所示：

最后用 CALCULATE 函数对 VALUES 函数获得的表格进行筛选，筛选的条件是TOPN 函数获取的表格，其实就相当于两个表格求交集，最后就获得了销售总额最高的季度。

10)销售总额_TOP_M

销售总额_TOP_M =  CALCULATE( VALUES('03-日历表'[月]), TOPN(1, SUMMARIZE( '03-日历表', '03-日历表'[月], "汇总", '度量值'[销售总额_本年] ), [汇总], DESC ) )

》》代码显示不全请在代码区域左右滑动

代码解析：公式同上，只是将 [03-日历表] 中的 [季度] 换成 [月]

11)销售总额_TOP_W

销售总额_TOP_W =  CALCULATE( VALUES('03-日历表'[周]), TOPN(1, SUMMARIZE( '03-日历表', '03-日历表'[周], "汇总", '度量值'[销售总额_本年] ), [汇总], DESC ) )

》》代码显示不全请在代码区域左右滑动

代码解析：公式同上，只是将 [03-日历表] 中的 [月] 换成 [周]

12)销售总额_TOP_D

销售总额_TOP_D =  CALCULATE( VALUES('03-日历表'[日期]), TOPN(1, SUMMARIZE( '03-日历表', '03-日历表'[日期], "汇总", '度量值'[销售总额_本年] ), [汇总], DESC ) )

》》代码显示不全请在代码区域左右滑动

代码解析：公式同上，只是将 [03-日历表] 中的 [周] 换成 [日期]