PowerBI-powerpivot学习及分析看板模板

powerBI简介

PowerB是用于数据分析和数据可视化工具Excel的上位替代品，市面上包含PowerBI的工具统称为BI工具。

学习PowerBI，就是在学习Excel的Power Query、Power Pivot、Power Map、Power View这四个组件。

四组件

● PowerQuery；一种嵌入Excel数据处理技术，用于数据清洗，可创建唯一的表格形状，将数据进行清洗和加工，以满足数据分析需求；【可以基本忽略】

● PowerPivot：一种数据建模技术，用于创建数据模型，建立关系和创建计算，可以处理大型数据集，构建广泛的关系及创建复杂的计算；【主要是这个】

● Power View：一种数据可视化技术，用于创建交互式图表、图形、地图和其他视觉效果，可以直观的呈现数据信息和数据结果；【辅助于PowerPivot】

● Power Map：一种三维数据可视化工具，可以在三维地球或自定义地图上绘制地理和时态数据，用于地图的三位视觉可视化呈现。【辅助于PowerPivot】

PowerBI基础功能

获取数据

有两种选择：导入计算机内部文件或连接本地既有数据库。

视觉对象

PowerBI内嵌的数据图表模板。仅做功能的介绍：

KPI Indicator--就是KPI

Bullet Chart/Bullet Chart by OKViz--用来展示分类字段实际值与目标值的差值

Animated Bar Chart Race--动态可视化

WordCloud--词云

RadarChart--雷达图

Infographic Designer--信息图，将原有的描述统计常用图标（柱）换成一些自定义图标

Tornado chart--蝴蝶图，用于分类对比

Quadrant Chart--象限图，分2*2的维度信息

Histogram Chart--直方图

Pareto Chart--帕累托图

Bubble Chart by Akvelon--气泡图

Box and Whisker chart--箱线图

Grid by MAQ Software--数据表，有点像数据透视表

Sparkline by OKViz--共X轴折线图

Cluster Map--集群地图，也可以做气泡图

小技巧-切片器显示指定顺序

字段是需要按一定顺序的定类，比如季节，季度。这些字段放入切片器后可能没有按照其既定顺序排列，则要进行调整，步骤如下：

建立一个顺序维度表，即装载所有需求的定类类型和以数字或字母挂钩的顺序列。然后将这个表和原表关联起来。建立切片器时，点击按列排序，选择那个顺序列即可

RFM用户分层模型

介绍

RFM模型是衡量客户价值和客户创利能力的重要工具和手段，通过一个客户的近期购买行为、购买的总体频率以及消费额度三个指标来描述该客户的价值状况。

其中R是交易间隔，F是交易频次，M是交易额度

通过使用RFM模型对客户进行分类，可将客户分为8大客户群体（2是高价，1是低价）：

222-重要价值会员

221-潜力会员

212-重要深耕会员

211-新会员

122-重要召回会员

121-一般保持会员

112-重要挽留会员

111-流失会员

函数理解

MAXX 函数：MAXX(<table>, <measure>)

在给定的表中，对所有的行应用指定的度量值，返回这些度量值中的最大值。

EARLIER 函数：EARLIER(<expression>)

在当前的计算上下文中，返回之前迭代中计算的表达式的值。

SUMMARIZE函数：SUMMARIZE(table, [column], [expression], ...)

用于创建一个聚合表，该表可以包含列的分组和聚合值。

DISTINCTCOUNT函数：DISTINCTCOUNT(expression)

用于计算一个表达式中不同值的数量。这个函数通常用于计算表或列中的唯一值的数量。

SWITCH函数：SWITCH(expression, value1, result1, [value2, result2], ..., [default])

将expression中的任意符合value条件的转化为result条件
 

函数示例数据

实操

①对数据进行预处理

载入数据后，一般首先进行重复值，缺失值和异常值的处理。

可以直接右键某一字段进行重复值的删除。

对于缺失值，可以使用Power Query编辑器删除包含缺失值的行。

对于异常值，可以先用箱线图识别异常值所在，再决定对该异常值进行何种处理（删除或挑出）。

然后是数据的属性变换，除了一些可能常见的字符串和数值属性混淆以外，还应该将日期列类型更改为日期，这很重要。

这样，基本的预处理就完成了。在主页点击“关闭并应用”即可。

②计算R、F、M值

（1）首先在数据界面，将日期格式修改为yyyy/mm/dd

新建列，写入DAX函数，计算“最后一个交易日期”

latest_order_date=MAXX(FILTER('Orders',EARLIER('Orders'[Uid])='Orders'[Uid]),'Orders'[Order_Date])

逻辑：

• 迭代：表达式在 ‘Orders’ 表的上下文中迭代每一行。
• 筛选：对于当前Orders迭代中的每一行，FILTER 函数创建一个行的子集，其中包含所有具有相同 Uid 的订单。这是通过比较当前行的 Uid（通过 EARLIER 函数引用）与 ‘Orders’ 表中所有行的 Uid 来实现的。
• 最大值：在由 FILTER 创建的每个 Uid 的子集中，MAXX 函数找到 ‘Order_Date’ 列的最大值，即该 Uid 的最后一个订单日期。

最后一个交易日期的日期格式也自定义为yyyy/mm/dd格式

再新建一列，计算R的值，并将其转化为整数

R = DATE(当前年,当前月,当前天)-'Orders'[latest_order_date].[Date]

（2）接着选择“新建度量值”，再输入函数设置F

F = DISTINCTCOUNT('Orders'[Order_Id])

同样的方式设置M

M = SUM('Orders'[Pay_Amt])

（3）接着点击新建表，设置函数

RFM = SUMMARIZE('Orders','Orders'[Uid],'Orders'[R],"F",[F],"M",[M])

（4）计算R,F,M各自得分（新建列）

R得分 = IF('RFM'[R]<=AVERAGE(RFM[R]),2,1)

F得分 = IF('RFM'[F]>=AVERAGE(RFM[F]),2,1)

M得分 = IF('RFM'[M]>=AVERAGE(RFM[M]),2,1)

新开一列得到确切的RFM值：RFM = 'RFM'[R得分]&'RFM'[F得分]&'RFM'[M得分]

（5）最后使用SWITCH函数将其分为8类

会员分组 = SWITCH( 'RFM'[RFM],

"111","流失会员","112","重要挽留会员","121","一般保持会员","122","重要唤回会员","211","新会员","212","重要深耕会员","221","潜力会员","222","重要价值会员")

③数据可视化

点击模型，将orders表和RFM表用各自主键（uid）创联起来，此时可以研究例如：

（1）会员分布情况

点击簇状条形图，将会员分布拖到轴和值，值设置为计数。

（2）会员占比情况

点击圆环图，将会员分布拖到图例和值，值设置为计数。

（3）不同会员的业绩贡献率

点击瀑布图，将会员分布拖到类别，值设置为占M总和的百分比

（4）查看具体明细

将要查看的明细直接拖入到值

（5）其他突出的要呈现的信息

选择卡片图，拖入对应字段，修改命名即可

帕累托法则可视化

介绍

二八法则也叫帕累托法则， 源于经典的二八法则，该法则表示，20%的变量将直接产生80%的效果。在数据分析中，则可以理解为20%的数据产生了80%的效果，需要围绕这20%的数据进行挖掘，然后思考如何让其余的80%向这20%转化。

函数理解

CALCULATE函数：CALCULATE(expression, [filter1], [filter2], ...)

用于在给定一个或多个筛选条件的情况下重新计算一个表达式。它改变了上下文，允许你根据特定的筛选器来计算度量值或表达式。

ALLSELECTED函数：ALLSELECTED([table])

回当前报表筛选上下文中的所有选定项，但在计算时忽略任何其他筛选器。

SUMX函数：SUMX(table, expression)

table: 要对其执行计算的表。expression: 对于表中的每一行，要计算的表达式。

ROUND函数：ROUND(number, num_digits)

number: 要四舍五入的数值。num_digits: 指定要四舍五入到的位数

TOPN函数：TOPN(number_of_records, table, column, [order])

number_of_records: 整数，指定要返回的记录数。table: 要从中选择记录的表。column: 基于哪个列对表中的记录进行排序。[order]: 可选参数，指定排序顺序，可以是 ASC（升序）或 DESC（降序）。如果不指定，默认为 升序。

函数示例数据

实操

①导入数据，数据清洗-前述已讲

要确保有无重复值的列才可以使用下述建模，比如在这里[货号]就是无重复值的一个

②数据建模

帕累托图建模：

（1）计算销售金额

销售金额 = SUM('商品销量'[销售额])

（2）使用CALCULATE，FILTER函数计算累计销售额

累计销售额 = CALCULATE([销售金额],FILTER(ALLSELECTED('商品销量'),SUM('商品销量'[销售额])<=[销售金额]))

（3）计算总销售额

总销售额 = CALCULATE([销售金额],ALLSELECTED('商品销量'))

（4）累计占比

累计占比 = DIVIDE([累计销售额],[总销售额])

帕累托2/8的前20%（或者前重要的比例）建模：

（1）新建款式数量（销量）度量值和其20%款式数量的定义度量值：

款式数量（或者销量） = CALCULATE(DISTINCTCOUNT('商品销量'[货号]),'商品销量'[销售额]>0)

20%款式数量 = ROUND([款式数量]*0.2,0)

（2）前20%销售额，按照销售金额降序排列：

前20%销售额 = SUMX(TOPN([20%款式数量],ALL('商品销量'[货号]),[销售金额],DESC),[销售金额])

（3）前20%销售占比：

前20%销售占比=DIVIDE([前20%销售额],[销售金额])

③数据可视化

可将以上四个度量值与“商品号/货号等等”和“销售额”放入一块表中，用销售额逆序排列即可。

（1）可视化帕累托图

可以立一个表，将货号和销售额，销售金额，累计销售额，总销售额，累计占比放入。

设置一个80%分界线度量值：

80%分界线 = 0.8

在可视化中选择组合图，将货号拖入到共享轴（X轴），销售金额拖入到列值，累计占比和80%分界线拖入到行值，作为一个次坐标轴

（2）查看前20%商品销量情况

可以立一个表，跟帕累托图的表一致，拖入的行值用于分类，可以选择想要了解的定类字段

业绩分析管理系统

业绩分析是将销售员的业绩目标和实际销售情况放在一起进行衡量、评价，适用于做业绩监控体系。（回顾视觉对象即可自定义，比较重要的：①仪表盘②子弹图）

除此之外，powerbi还有许多可以用视觉对象做到的图，例如地图数据，动态数据（常见的动态排名可视化）等，使用方便简单。