用PowerBI分析上市公司财务数据（二）

那么如何才能建立合理模型呢？

笔者认为要具备以下条件：一是理解业务数据，知道主要分析的指标及潜在的报表分析需求；二是对DAX表达计算逻辑、特性有一定的了解。

题外话：

EXCEL中最难的函数可能就是查找引用函数了，如vlookup,index,match,lookup等，特别这些函数的数组用法，如果涉及到三维引用，大部分人都会弄晕，可能一个公式半天都弄不明白，也许好不容易弄明白了，过段时间又忘记了。不过这些在PBI中将不会存在，PBI将通过模型的建立，表与表之间的关联不再与数据呈现的物理位置有关，只需要理解“上下文”这个概念。

言归正传，下面来讲下如何建立模型：

第一步：分析数据特点

从数据的结构来讲，上市公司历年的财务报表，数据关系还是比较简单的，但是，数据也有其特点，如果没有财务基础知识的话，可能在写度量值时会犯一些错误。

首先要清楚：资产负债表是一类，利润表和现金流量表属另一类！

资产负债表是时点数据，它实际上是反映公司自成立以来每个时点资产负债情况，如果在后面度量值写成=SUM(资产负债表项目)，那这个度量值在一个时间段内将毫无意义，因为把各个时点的数据直接相加，没有实际意义。

而利润表和现金流量表则是时期数据，是反映一个时间段内收入、利润、现金流量变化的数据，但是由于利润表和现金流量表是本年累计数，即报表日期中2019年3月31日是指1-3月累计数，2019年6月30日是指1-6月累计数，由于这个原因，直接写度量值=SUM(利润)可能也没有意义，但SUM(利润)在一季度、二季度、三季度、四季度单独的筛选下又有意义，比如筛选一季度数据，则SUM(利润)代表所有年份一季度利润的合计。

第二步：确定分析维度

首先，时间和公司名称这两个维度是必须，也很容易发现。但是后面报表科目应该怎么处理呢？其实我们能够发现，后面的报表科目其实也是一个维度，按照“用PBI分析上市公司财务数据（一）”处理完成后的数据，其实是一个二维表.

如果后续导出到EXCEL使用，或是直接打印出来阅读，或是用来做某几个指标的数理统计分析，这类二维表是合适的，但如果需要在PBI中分析，呈现更细致的微观分析报告，那么需要对数据进行降维处理，也就是要将后面的科目列进行逆透视操作。具体操作如下：

在PQ编辑器中对查询生成的资产负债表选择除公司代码、报告日期之外的其他列后右击，选择逆透视列，完成后更改下列名，如下：

利润表和现金流量表也按这样操作，最后三张财务报表列数完全相同，都是四列，分别为公司代码、报告日期、科目、值。为了后续能够最大限度实现度量值的复用，维护简单，我们可以接下来将这三张表合并：选择这三张表

然后在功能区中选择追加查询=》将查询追加为新查询

最后，将生成的本表改名为财务报表。

虽然分析维度可以在模型建立后反复修改或添加，但由于分析维度对模型建立影响较大，因此，模型建立初最好要确定主要的分析维度。

第三步：确定分析模型所需的表， 并设定表与表之间的关系

根据上面第二步分析得知，我们至少要有三个维度表，即时间、公司、科目维度表，有了这三个维度表后，我们就可以在后面分析中根据这些维度对数据进行切片计算。

1、建立时间表/日期表

由于待分析的财务报表数据中，日期字段仅一个即报表日期，因此建立日期表我们无需考虑过多因素，直接用DAX函数 calendarauto()建立，该函数直接扫描现有模型中的日期，自动建立涵盖现有日期字段的日期表。

具体操作如下：点击建模=》新表 ，输入 ：日期表 = CALENDARAUTO()

这时模型中自动生成了Date 字段 ，然后依次点击新建列 分别建立以下字段：

年 = YEAR([Date])

季度 = ROUNDUP(MONTH([Date])/3,0)

季度名称 = "Q"&[季度]

年季名称 = [年]&[季度名称]

在自动生成的日期建立这些字段主要是便于后期筛选和计算。

2、 建立公司维度表

公司维度表，即待分析的公司基本信息，在“用PBI分析上市公司财务数据（一）”中其实就已经在上交所网上得到了相关的数据，如下：

我们把它单独保存一张表，以便形成后续在公司维度上进行。

3、 建立科目维度表

通过前面的分析，我们还需要一张表科目信息表用来关联财务报表数据，由于科目维度信息表比较固定，更新次数少，我们可以先在EXCEL中手工维护好后导入PBI中。我们将科目维度表整理成以下样式，其中科目对照列是用来与财务报表建立关联的列。

这里需要注意：由于科目对照列是主键，需要值唯一，由于现金流量表中的补充资料用到了部分利润表及资产负债表项目，导致值重复，为了实现值唯一，还需要对现金流量表的项目特殊处理，如下：

在PQ中选择现金流量表科目列=》在功能区选择转换=》格式=》添加后缀

这个后缀可以随便加，主要是为了和利润表、资产负债表区分，保证唯一，不过为了美观，我建议加了一个人眼看不出来的字符，但计算机又视为不同，经测试下面这个字符符合要求，字符代码为127

我们把这个字符复制后粘贴到下面输入框中，确定后即更新后完成整理。

同样，我们也在科目维度表中有关现金流量表科目的后面增加这个字符。

考虑到原来的科目中每个都有万元，直接显示显示出来不好看，因此，我们增加一列用来在报表可视化中显示出来的名称即项目名称列，为了让显示出来的项目显示出层级，更加好看，可以在项目名称的前后增加这个字签，模拟缩进效果，（该字符在查询时可见，在可视化时不可见）如下：

另外，考虑到科目显示排序的问题，我们由PQ自动生成一列索引列。同时回到建模界面，将项目名称设置按列排序=》索引

最后，我们将日期表、公司信息表、科目维度表，财务报表 这四张表建立如下关系。

第四步：编写度量值

先编写一个基础度量值：

值合计 = SUM('财务报表'[值])

1、资产负债表项目

资产负债表项目我们一般是分析期初和期末金额及结构的变化，因此我们需要建立以下度量：

（1）期末金额

期末 =
VAR EndDate=MAX('日期表'[Date])
//取得所选日期的最大值
VAR EndAmount=CALCULATE([值合计],
   '日期表'[Date]=EndDate)
//计算期末金额
return
  IF(EndAmount=0,BLANK(),EndAmount)
//隐藏项目金额为0的科目

（2）年初金额

期初 =
VAR CURyear=MAX('日期表'[年])
VAR beginAmt=CALCULATE([期末],
  FILTER(ALL('日期表'),
  '日期表'[年]=CURyear-1 
  && '日期表'[季度]=4))
return
    beginAmt

（3）变动金额

变动 = IF(ISBLANK([期末]),
          BLANK(),
          [期末]-[期初])

（4）变动率

变动率 = DIVIDE([变动],[期初])

（5）重点关注指标

期末流动比率 = 
DIVIDE(
 CALCULATE([期末] , 
   '科目表'[科目对照]="流动资产合计(万元)"),
 CALCULATE([期末] , 
   '科目表'[科目对照]="流动负债合计(万元)"))

期末资产负债率 = 
DIVIDE( 
  CALCULATE([期末] , 
  '科目表'[科目对照]="负债合计(万元)"), 
  CALCULATE([期末] , 
  '科目表'[科目对照]="资产总计(万元)"))

期末现金比率 = 
DIVIDE( CALCULATE([期末] , 
 '科目表'[科目对照] in
 {"货币资金(万元)","交易性金融资产(万元)"}), 
CALCULATE([期末] , 
'科目表'[科目对照]="流动负债合计(万元)"))

第五步，数据可视化

1. 资产项目，选择矩阵

依次将相关度量值，加入值，项目名称加入到行

同时将科目表的类型字段，加入到该矩阵的筛选器，并筛选资产

2. 负债项目与资产项目一样，可直接将矩阵复制一个，将筛选器改为权益

3、选择卡片图，依次将流动比率、现金比率、资产负债率用三个卡片图显示

4、将公司简称、年、季度名称分别加入到三个切片器

调整格式，效果如下：

本节主要介绍了如何建立一个简单模型的思路和方法，选取的也是相对简单的有关资产负债表项目的度量值编写，有关利润表和现金流量表的分析将在后续文章介绍。

作者 张震 | 编辑 沐笙