以下内容皆来自b站孙兴华老师,请多多支持,老师的b站 UID:437239552
dax笔记
- 应用于百万级别的数据,非大数据。大数据请了解Numpy、Pandas、Matplotlib
a. 尽量避免多表的关系建模,建议使用SQL拿到一张干净的表,再做数据分析
b. 把精力用在数据分析上面,而不是放在表与表的关系上面 - 化繁为简:
a. 数据抓取交给SQL
b. 数据清洗交给PowerQuery,虽然BI支持文本函数,但是不建议在BI中操作
c. 自动化交给VBA(如多表合并等)
d. 能在Excel中处理的数据,就不要放到PowerBI里面计算 - 专业的工具做专业的事:
- 尽量减少度量值的使用,因为脑子会乱,如果你有一张干净的表,多建几列没有问题
- 遇到困难,尽量避开,而不是停下来研究,你在今后的工作中,会发现解决方法。
重要的事情说三遍:化繁为简!化繁为简!化繁为简!
前言:PowerBI的个人建议
2020年5月4日 3:40
分区 DAX语言 的第 1 页
一、数据分析表达式
2020年5月3日 22:17
分区 DAX语言 的第 2 页
度量值 = CALCULATE([销售量],‘产品表’[商品类别]=“蔬菜”)
度量值名称
等号
DAX函数
可重复引用度量值
引用指定表中的指定列
1、书写规则
2020年5月3日 22:04
分区 DAX语言 的第 3 页
算术运算符 含义 示例
- 加 2+1=3
- 减 3-1=2
- 乘 3*3=9
/ 除 3/3=1
^ 幂 2^3=8
比较运算符 含义
= 等于
大于
< 小于
= 大于或等于
<= 小于或等于
<> 不等于
文本运算符 含义
& 连接
逻辑运算符 含义
&& 相当于and
|| 相当于or
in 包含
not in 不包含
2、运算符
2020年5月3日 22:18
分区 DAX语言 的第 4 页
二、计算器与筛选器
2020年5月3日 22:53
分区 DAX语言 的第 5 页
一、求和
[度量值]=Sum(‘销售表’[销售数量])
二、平均值
[度量值]=Average(‘销售表’[销售数量])
三、最大小最值
[度量值]=Max(‘销售表’[销售数量])
[度量值]=Min(‘销售表’[销售数量])
四、计算行数
[订单数量]=Countrows(‘销售表’)
五、其它函数
CountA函数:计算列中单元格不为空的数目
Countblank函数:计算列中单元格为空白数量
Distinctcount函数:计算列中非重复值的数量
举例:
单店销售量 = [销售总量]/[门店数量]
Distinctcount(‘销售表’[店号])
sum(‘销售表’[销售数量])
1、计算器:聚合函数
2020年5月3日 22:28
分区 DAX语言 的第 6 页
度量值 = Calculate([销售总量],‘商品表’[商品种类]=“蔬菜”,‘商品表’[商品规格]=“盒装”)
计算器【列求和】
筛选器【限定表的筛选条件】
计算逻辑:
1、筛选器先工作
2、Calculate拿到筛选后的度量值
多条件时,建议使用in或not in
度量值 = Calculate([销售总量],‘商品表’[商品种类] in {“蔬菜”,“水果”,“水产”})
度量值 = Calculate([销售总量], not ‘商品表’[商品种类] in {“蔬菜”,“水果”,“水产”}) 2、筛选器:Calculate函数
2020年5月3日 22:29
分区 DAX语言 的第 7 页
Calculatetable(表,筛选条件) 与Calculate的区别就是,它可以多表运作筛选,返回一张表
例如:
Calculatetable(‘销售表’,‘商品表’[商品种类]=“水果”,‘商品表’[规格]=“盒”)
- 筛选表:Calculatetable函数
2020年5月9日 18:20
分区 DAX语言 的第 8 页
三、其它常用筛选函数
2020年5月4日 3:17
分区 DAX语言 的第 9 页
ALL函数的功能:清除筛选条件
ALL函数的应用:清除表中所有行的筛选器以及创建针对表中所有行的计算非常有用。
ALL函数的返回值:已清除筛选的表或列
注意事项:ALL 函数的参数必须是对表的引用或对列的引用。 不能将 ALL 函数与表的表达式或列的表达式一起使用。
All销售量1= Calculate([销售总量],All(‘销售表’))
商品种类 散 盒 总计
蔬菜 52730 52730 52730
水果 52730 52730 52730
肉品 52730 52730 52730
水产 52730 52730 52730
总计 52730 52730 52730
All销售量2= Calculate([销售总量],All(‘商品表’[商品种类]))
商品种类 散 盒 总计
蔬菜 31638 21092 52730
水果 31638 21092 52730
肉品 31638 21092 52730
水产 31638 21092 52730
总计 31638 21092 52730
All销售量3= Calculate([销售总量],All(‘商品表’[商品种类],‘商品表’[商品规格]))
All销售量3= Calculate([销售总量],All(‘商品表’),ALL(‘店铺表’))
一般ALL(表名)会在计算占比时使用
占比=[销售总量]/[ALL销售量1]
注:配合切片器使用,可以筛选门店、日期等
注意:ALL函数在引用列的时候,必需与矩阵的行和列在同一张表
假设,你ALL引用的是商品表,但是矩阵行和列来自店铺表就会出错。
必须保证,ALL所清除的筛选列和初始筛选条件中的筛选列是同一张表的同一列。
引用多个列时,必须是同一张表
3.1 ALL函数 【清除筛选】
2020年5月4日 1:57
分区 DAX语言 的第 10 页
Allexcept函数:除…之外
语法:Allexcept(表名[字段名4]) 等同于 All(表名[字段名1],表名[字段名2],表名[字段名3])
3.2 Allexcept 【清除筛选,除…之外】
2020年5月4日 2:44
分区 DAX语言 的第 11 页
商品种类 商品种类 占比1
蔬菜 蔬菜 36.21%
水果 水果 37.15%
肉品 肉品 17.80%
水产 总计 91.16%
占比1=[销售总量]/[ALL销售量1]
All销售量1= Calculate([销售总量],All(‘销售表’))
如果想让这里显示100%,使用Allselceted函数
Allselceted销售量= Calculate([销售总量],Allselceted(‘销售表’))
占比2=[销售总量]/[Allselceted销售量]
商品种类 商品种类 占比1 占比2
蔬菜 蔬菜 36.21% 39.72%
水果 水果 37.15% 41.58%
肉品 肉品 17.80% 18.70%
水产 总计 91.16% 100.00%
总结:
1、当ALL参数为表时,忽略所有的筛选条件,无论是该图表内还是外部切片器
2、当ALL参数为列时,忽略该列筛选,其它图表字段或外部筛选对基产生作用
3、当ALLselected参数为表时,忽略该图表的筛选条件,其它外部筛选对其产生作用
4、当ALLselected参数为列时,忽略该图表在该列的筛选条件,其它该图表字段或外部筛选对其它产生作用。
3.3 Allselceted函数 【占比显示100%】
2020年5月4日 3:18
分区 DAX语言 的第 12 页
DISTINCTCOUNT(表名[字段名]) 返回:去重后,唯一值的列
DISTINCTCOUNT(表名) 返回:只包含非重复行的表
3.4 DISTINCT 去重复
2020年5月4日 3:34
分区 DAX语言 的第 13 页
返回一个表,用于表示另一个表或表达式的子集,不能单独使用
Filter函数对筛选的表进行横向的逐行扫描,这样的函数叫迭代函数。
Countrows(Filter(表,筛选条件))
Calculate(表达式(度量值),Filter(‘表名’, 筛选条件))
一、效果相同,为什么使用Filter函数?
销售数量 = SUM(‘销售明细表’[销量数量] )
销量a = CALCULATE([销售数量],FILTER(‘商品表’,‘商品表’[类别]=“蔬菜” && ‘商品表’[规格]=“盒”))
销量b = CALCULATE([销售数量],‘商品表’[类别]=“蔬菜”,‘商品表’[规格]=“盒”)
二、什么时候使用Filter函数
在Calculate函数中的直接筛选条件里,我们只能输入:
‘表’[列] = 固定值 或 ‘表’[列] <> 固定值
‘表’[列] >= 固定值 或 ‘表’[列] <= 固定值
‘表’[列] > 固定值 或 ‘表’[列] < 固定值
但是遇到如下情况,就要使用Filter函数
[列]=[度量值]、 [列]=公式、[列]=[列] [度量值]=[度量值]、 [度量值]=公式、[度量值]=固定值
例如:盒装苹果销量200盒以上的门店总共卖了多少盒?
Filter销售量 = Calculate([销售量],Filter(‘店铺表’,[销售量]>200))
商品种类 年份季度 销售量 Filter销售量
西红柿(盒) 2019Q1 1856 1485
黄瓜(盒) 2019Q2 5693 4554
苹果(盒) 2019Q3 9981 7985
梨(盒) 2019Q4 15691 12553
总计 33221 26577 - 尽量在基础表中使用,不要在数据表中使用
- 能使用Calculate函数直接完成,就不要使用Filter函数
三、注意事项
注意:这里必须是度量值大于200
不能是Sum(‘销售表’[销售数量])>200
3.5 高级筛选器:Filter函数【行上下文】
2020年5月4日 3:39
分区 DAX语言 的第 14 页
分区 DAX语言 的第 15 页
筛选上下文:针对列的筛选
行上下文:行上下文,行上下文=当前行 - 行上下文不会自动转换成筛选上下文,如果需要转换,则必须使用Calculate函数
- 度量值自带天然的Calculate函数
重点:
例如:
度量值 = sum(‘销售表’[销售数量]) 把度量值放到矩阵中就可以实现自动筛选
但是如果,新建列的时候使用了 新列名 = sum(‘销售表’[销售数量]) 那么每一行的值都是最终的sum(‘销售表’[销售数量]) 的总值,不会实现筛选
这时,使用Calculate函数转换,即可在新建列上实现自动筛选,新列名=Calculate(Sum(‘销售表’[销售数量]))
回来看一下Filter函数的案例:【因为Filter是逐行扫描,是行上下文,需要转换】
Filter销售量 = Calculate([销售量],Filter(‘店铺表’,[销售量]>200)) 正确
Filter销售量 = Calculate([销售量],Filter(‘店铺表’,Sum(‘销售表’[销售数量])>200)) 错误
Filter销售量 = Calculate([销售量],Filter(‘店铺表’,Calculate(Sum(‘销售表’[销售数量]))>200)) 正确
为什么[销售量]就不用套Calculate?
因为所有的度量值都自带Calculate函数的功能
销售量 = Sum(‘销售表’[销售数量]) 等同于 销售量 = Calculate(Sum(‘销售表’[销售数量]))
四、理解上下文
2020年5月4日 3:49
分区 DAX语言 的第 16 页
语法:DIVIDE(分子,分母,[替换结果])
替换结果可以省略不写,省略时返回为空。
五、Divide函数:安全除法
2020年5月5日 0:08
分区 DAX语言 的第 17 页
If(西瓜坏了,换货,[退货])
第三参数可以省略不写,省略时返回为空。
If适合条件比较少的时候使用,如果条件太多,嵌套太多,不方便使用
称呼 = IF([性别]=“男”,“先生”,“女士”)
switch(表达式,值1,结果1,…,[else])
最后一个参数可以省略不写,省略时返回为空。
月份 = SWITCH(‘switch函数’[月],
1, “一月”, 2,“二月”,
3, “三月”, 4,“四月”,
5, “五月”, 6,“六月”,
7, “七月”, 8,“八月”,
9, “九月”, 10,“十月”,
11,“十一月”, 12,“十二月”,
“未能识别”)
六、if/Switch函数:逻辑判断
2020年5月5日 1:28
分区 DAX语言 的第 18 页
销售金额 = [销售数量]*RELATED(‘商品表’[售价])
Related函数:(多端找一端)(事实表找维度表)(数据表找基础表)
毛利额 = [销售金额]-([销售数量]*RELATED(‘商品表’[进价] ))
度量值销售金额 = SUMX(‘销售表’,‘销售表’[销售数量]RELATED(‘商品表’[售价]))
Relatedtable函数:(一端找多端)(维度表找事实表)(基础表找数据表)
COUNTROWS(表) 统计表格中的行数
例如:在商品表中,添加一列订单数量。【商品表是一端,再多端的销售表里找】
订单数量 = COUNTROWS(RELATEDTABLE(‘销售表’))
Lookupvalue函数:就是Vlookup
语法:Lookupvalue(引用哪张表哪个列,那张表的搜索范围,找自己表里的哪个列)
新建列售价 = LOOKUPVALUE(‘商品表’[售价],‘商品表’[商品编码],‘销售表’[商品编码])
如果两个表都有如下字段,不用合并后去查找,使用多条件即可
可以是多个条件,例如:
条件1:‘商品表’[商品种类],‘销售表’[商品种类]
条件2:‘商品表’[杯型],‘销售表’[杯型]
七、关系函数
2020年5月5日 1:33
分区 DAX语言 的第 19 页
八、时间智能函数
2020年5月5日 1:54
分区 DAX语言 的第 20 页
日期表 = ADDCOLUMNS(
CALENDAR(date(2019,1,1),date(2019,12,31)),
“年”, YEAR ( [Date] ),
“季度”, ROUNDUP(MONTH([Date])/3,0),
“月”, MONTH([Date]),
“周”, weeknum([Date]),
“年季度”, year([date]) & “Q” & ROUNDUP(MONTH([Date])/3,0),
“年月”, year([Date]) * 100 + MONTH([Date]),
“年周”, year([Date]) * 100 + weeknum([Date]),
“星期几”, WEEKDAY([Date])
)
8.1 日期表
2020年5月6日 18:48
分区 DAX语言 的第 21 页
计算类函数:返回的是值不是表,他们可以让公式变短,但是运算意义上没有差别
Totalmtd/ytd/qtd 从月初(年初、季初)至今的累计值
语法: Totalmtd(表达式或度量值,日期列,【筛选表达式】)
月度累计销售额 = totalmtd([销售量],‘日期表’[日期])
8.2 累计时间段
2020年5月7日 0:31
分区 DAX语言 的第 22 页
8.3 Dateadd函数 【同期、环比】
2020年5月7日 0:46
分区 DAX语言 的第 23 页
返回由一列构成的一个表,该表包含来自指定表或列的非重复值。表中重复值将被删除,返回唯一值。
Values函数生成的表是一张虚拟表。
在很多时候使用Filter、Calculate、Countrows、SumX、TopN这些函数,需要引用表而不能直接引用列。Values函数的功能是把列转化成包含列的表,这时只需
要嵌套一个Values函数就可以引用了。
注意:这张表,是不重复的信息表!
假设我没有多张表的关系连接,所有数据都在销售表中:
在新建表那里生成一张虚拟表,从销售数据表中拿到的不重复门店,这样就可以统计销售量大于300的门店销售量了
经典语句: Calculate([度量值],Filter(Values(‘表’[列名]),…))
九、Values函数:不重复值
2020年5月5日 2:05
分区 DAX语言 的第 24 页
返回值:“真”或“假”
经典语句: if(Hasonevalue(‘表名’[列名]),[度量值],blank())
例:if(Hasonevalue(‘日期’[年份季度]),[filter销售量],blank())
Filter销售量 = Calculate([销售量],Filter(‘店铺表’,[销售量]>300))
店铺表 = Values(‘销售数据表’[门店])
说明:当求总计时,筛选上下文件是2019年全年,在日期表中,2019年有4个季度,不是唯一值,所以返回空值
十、Hasonevalue函数:只有一个值
2020年5月5日 2:14
分区 DAX语言 的第 25 页
当指定列中只有一个值时返回该值,否则返回替代结果,省略则返回空值。
SELECTEDVALUE 与 IF+HASONEVALUE 等价,而且公式更简洁
if(Hasonevalue(‘表名’[列名]),返回值,blank())
SELECTEDVALUE (‘表名’[列名],【代替(省略返回空)】) • 如果是唯一值,那就返回值,否则返回空
• 当指定列中只有一个值时返回该值,否则返回替代结果,省略则返回空值。
【更简洁公式】SELECTEDVALUE
2020年5月8日 12:13
分区 DAX语言 的第 26 页
SumX、AverageX、MaxX、MinX…它们与Filter函数属于一种类型——行上下文函数
语法:SumX(‘表名’,算术表达式)
[销售数量][单价] 1、逐行扫描
2、执行运算,每一行都会返回一个值,例如 15x1.1=16.5
3、SumX函数记住了每一行返回的值,最后把所有的值相加求和。
十一、SumX函数 【行上下文】
2020年5月5日 2:20
分区 DAX语言 的第 27 页
作用:使用Earlier函数得到当前行
经典语句:
Calculate([度量值],Filter(‘表名’,[列名]=Earlier([列]))
它与索引列结合起来,可以去关联上一行或上几行
Calculate([度量值],Filter(‘表名’,[索引]=Earlier([索引]-1))
应用:观察新建列的数值是什么?整个国产的销量
度量值· 销售量 = SUM(‘销售表’[销售数量] )
新建列· 列 = CALCULATE([销售量],FILTER(‘产品表’,‘产品表’[巧克力种类]=EARLIER(‘产品表’[巧克力种类])))
这个函数需要在实践中不断体验,如果你觉得不好理解,没关系,下节课我们讲VAR函数时会介绍一种可以取代Earlier函数的方法。
十二、Earlier函数:当前行 【行上下文】
2020年5月5日 2:19
分区 DAX语言 的第 28 页
RankX(表名,表达式或度量值)
RankX(all(‘门店信息表’),[销售总量])
应用:店铺的销售量排名
销售量 = SUM(‘销售表’[销售数量])
销售量排名 = RANKX(ALL(‘店号’),[销售量] )
十三、RankX和TopN函数:排名 【行上下文】
2020年5月5日 2:33
分区 DAX语言 的第 29 页
十四、VAR/Return函数:
2020年5月5日 2:20
分区 DAX语言 的第 30 页
语法:SummarizeColumns ( 第1参数,第2参数,第3参数,第4参数)
一、返回不重复姓名
不重复姓名 = SummarizeColumns(‘重复姓名’[姓名])
二、返回多列不重复
多列不重复 = SummarizeColumns(‘多列重复’[年份],‘多列重复’[姓名])
三、返回汇总表【常用】
汇总表 = SUMMARIZECOLUMNS(‘分组求和’[年份],‘分组求和’[姓名], “总分”,CALCULATE(sum(‘分组求和’[成绩])))
四、返回带筛选功能的汇总表【常用】
汇总表筛选 = SUMMARIZECOLUMNS(‘分组求和’[年份],‘分组求和’[姓名], Filter(‘分组求和’,‘分组求和’[科目]=“数学”),“总分”,CALCULATE(sum(‘分组求和’[成
绩])))
表名[字段名]
表名1[字段名1],表名2[字段名2] 注:可以不是同一张表
表名1[字段名1],表名2[字段名2],新字段名,新字段聚合表达式 注:可以省略Calculate
表名1[字段名1],表名2[字段名2],Filter(表名,表名[字段名]=“筛选条件”),新字段名,新字段聚合表达式
注:可以省略Calculate
十五、分组聚合SummarizeColumns函数
2020年5月6日 15:55
分区 DAX语言 的第 31 页
交叉相同:intersect(表1,表2)
除去相同:Except(表1,表2)
全部:Union(表1,表2)
表1 表2
交叉
表1 表2
除去 全部
表1 表2
十六、交叉相同、除去相同、全部
2020年5月9日 18:51
分区 DAX语言 的第 32 页
find(查找哪个字符,去哪里查找,从哪一个位置查找,如果找不到返回什么值) 第三和四参数可以省略,返回字符在第几位
search(查找哪个字符,去哪里查找,从哪一个位置查找,如果找不到返回什么值) 第三和四参数可以省略,返回字符在第几位
注意:Find查找字符区分大小写,search不区分大小写,其它功能完全一样。
left从左向右取=left([字段名],取几个字符)
right从右向左取=right([字段名],取几个字符)
mid从中间开始取=mid([字段名],从第几个取,取几个)
len长度=len([字段名])
十七、文本函数
2020年5月12日 10:02
分区 DAX语言 的第 33 页
附:案列
2020年5月5日 0:06
分区 DAX语言 的第 34 页 - 在店铺表中新建一列,这一列中的数据就是月销售量的目标值,如图1
- 新建度量值:[销售总量] = sum(‘销售表’[销售数量])
- 新建度量值:完成目标的门店销售量 = Calculate([销售量],Filter(‘店铺表’,‘店铺表’[月销售目标值]<=[销售总量])
- 新建度量值:未完成目标门店销售量 = Calculate([销售量],Filter(‘店铺表’,‘店铺表’[月销售目标值] > [销售总量])
- 将度量值放入矩阵
步骤分析:
小技巧:双击矩阵表栏中的"值"项目,修改度量值显示的名称,可以将美化度量值名称的长度。
拓展:如果有些门店月销售量目标值定的太高,想把月销量目标值超过全部门店销售总量1/3的分店找出来
目标设定过高的门店销售量 = Calculate([销售量],Filter(‘店铺表’,‘店铺表’[月销量目标值]>sum(‘销售表’[销售数量])/3))
Filter(‘表名’,‘表名’[列]=公式)
Filter(‘表名’,‘表名’[列]=[度量值])
店号 店名 月销量目标值
1 XXX 66
2 XXX 73
3 XXX 296
4 XXX 296
5 XXX 179
如图1
001.月销售量完成
2020年5月5日 0:07
分区 DAX语言 的第 35 页
表2 = FILTER(‘销售表’,‘销售表’[顾客ID]=9527 )
想知道每笔订单客户第几次购买:自动更新序号
借助索引列
第几次购买 = COUNTROWS(
filter(‘销售表’,
‘销售表’[顾客ID]=EARLIER(‘销售表’[顾客ID])
&&
‘销售表’[索引]<=EARLIER(‘销售表’[索引]
))
002.计算同一顾客多次购买的情况
2020年5月5日 0:25
分区 DAX语言 的第 36 页
基础篇
算术运算符 含义 示例
- 加 2+1=3
- 减 3-1=2
- 乘 3*3=9
/ 除 3/3=1
^ 幂 2^3=8
比较运算符 含义
= 等于
大于
< 小于
= 大于或等于
<= 小于或等于
<> 不等于
文本运算符 含义
& 连接
逻辑运算符 含义
&& 相当于and
|| 相当于or
in 包含
not in 不包含
DAX语言各种:运算符
2020年5月3日 22:18
分区 重制版 的第 1 页
一、将度量值全部放在一个表中
二、度量值举例
总销量 = SUM(‘销售表’[销售数量])
平均销量 = AVERAGE(‘销售表’[销售数量])
最大值 = Max(‘销售表’[销售数量])
最小值 = Min(‘销售表’[销售数量])
订单数量 = Countrows(‘销售表’)
门店数量 = DISTINCTCOUNT(‘销售表’[店号])
单店销售量 = [总销量]/[门店数量]
CountA函数:计算列中单元格不为空的数目
Countblank函数:计算列中单元格为空白数量
将字段区隐藏再显示,不仅表格图标发生变化,而且度量值集群表会自动排列在最上方,方便阅读
01.度量值
2020年6月27日 11:25
分区 重制版 的第 2 页
销售金额 = [销售数量]*RELATED(‘商品表’[售价])
Related函数:(多端找一端)(事实表找维度表)(数据表找基础表)
毛利额 = [销售金额]-([销售数量]*RELATED(‘商品表’[进价] ))
度量值销售金额 = SUMX(‘销售表’,‘销售表’[销售数量]*RELATED(‘商品表’[售价]))
Relatedtable函数:(一端找多端)(维度表找事实表)(基础表找数据表)
COUNTROWS(表) 统计表格中的行数
例如:在商品表中,添加一列订单数量。【商品表是一端,再多端的销售表里找】
订单数量 = COUNTROWS(RELATEDTABLE(‘销售表’))
Lookupvalue函数:就是Vlookup
语法:Lookupvalue(引用哪张表哪个列,那张表的搜索范围,找自己表里的哪个列)
新建列售价 = LOOKUPVALUE(‘商品表’[售价],‘商品表’[商品编码],‘销售表’[商品编码])
如果两个表都有如下字段,不用合并后去查找,使用多条件即可
可以是多个条件,例如:
条件1:‘商品表’[商品种类],‘销售表’[商品种类]
条件2:‘商品表’[杯型],‘销售表’[杯型]
02.新建列与关系函数
2020年5月5日 1:33
分区 重制版 的第 3 页
03.最强大的引擎与筛选表
2020年6月26日 22:41
分区 重制版 的第 4 页
返回一个表,用于表示另一个表或表达式的子集,不能单独使用
Filter函数对筛选的表进行横向的逐行扫描,这样的函数叫迭代函数。
Countrows(Filter(表,筛选条件))
Calculate(表达式(度量值),Filter(‘表名’, 筛选条件))
一、效果相同,为什么使用Filter函数?
销售数量 = SUM(‘销售明细表’[销量数量] )
销量a = CALCULATE([销售数量],FILTER(‘商品表’,‘商品表’[类别]=“蔬菜” && ‘商品表’[规格]=“盒”))
销量b = CALCULATE([销售数量],‘商品表’[类别]=“蔬菜”,‘商品表’[规格]=“盒”)
二、什么时候使用Filter函数
在Calculate函数中的直接筛选条件里,我们只能输入:
‘表’[列] = 固定值 或 ‘表’[列] <> 固定值
‘表’[列] >= 固定值 或 ‘表’[列] <= 固定值
‘表’[列] > 固定值 或 ‘表’[列] < 固定值
但是遇到如下情况,就要使用Filter函数
[列]=[度量值]、 [列]=公式、[列]=[列] [度量值]=[度量值]、 [度量值]=公式、[度量值]=固定值
例如:盒装苹果销量200盒以上的门店总共卖了多少盒?
Filter销售量 = Calculate([销售量],Filter(‘店铺表’,[销售量]>200))
商品种类 年份季度 销售量 Filter销售量
西红柿(盒) 2019Q1 1856 1485
黄瓜(盒) 2019Q2 5693 4554
苹果(盒) 2019Q3 9981 7985
梨(盒) 2019Q4 15691 12553
总计 33221 26577
- 尽量在基础表中使用,不要在数据表中使用
- 能使用Calculate函数直接完成,就不要使用Filter函数
三、注意事项
注意:这里必须是度量值大于200
不能是Sum(‘销售表’[销售数量])>200 - 高级筛选器Filter
2020年5月4日 3:39
分区 重制版 的第 5 页
分区 重制版 的第 6 页
语法:SummarizeColumns ( 第1参数,第2参数,第3参数,第4参数)
一、返回不重复姓名
不重复姓名 = SummarizeColumns(‘重复姓名’[姓名])
二、返回多列不重复
多列不重复 = SummarizeColumns(‘多列重复’[年份],‘多列重复’[姓名])
三、返回汇总表【常用】
汇总表 = SUMMARIZECOLUMNS(‘分组求和’[年份],‘分组求和’[姓名], “总分”,CALCULATE(sum(‘分组求和’[成绩])))
四、返回带筛选功能的汇总表【常用】
汇总表筛选 = SUMMARIZECOLUMNS(‘分组求和’[年份],‘分组求和’[姓名], Filter(‘分组求和’,‘分组求和’[科目]=“数学”),“数学”,CALCULATE(sum(‘分组求和’[成
绩])))
表名[字段名]
表名1[字段名1],表名2[字段名2] 注:可以不是同一张表
表名1[字段名1],表名2[字段名2],新字段名,新字段聚合表达式 注:可以省略Calculate
表名1[字段名1],表名2[字段名2],Filter(表名,表名[字段名]=“筛选条件”),新字段名,新字段聚合表达式
注:可以省略Calculate
05.人生处处是SQL
2020年5月6日 15:55
分区 重制版 的第 7 页
筛选上下文:针对列的筛选
行上下文:行上下文,行上下文=当前行 - 行上下文不会自动转换成筛选上下文,如果需要转换,则必须使用Calculate函数
- 度量值自带天然的Calculate函数
重点:
例如:
度量值 = sum(‘销售表’[销售数量]) 把度量值放到矩阵中就可以实现自动筛选
但是如果,新建列的时候使用了 新列名 = sum(‘销售表’[销售数量]) 那么每一行的值都是最终的sum(‘销售表’[销售数量]) 的总值,不会实现筛选
这时,使用Calculate函数转换,即可在新建列上实现自动筛选,新列名=Calculate(Sum(‘销售表’[销售数量]))
回来看一下Filter函数的案例:【因为Filter是逐行扫描,是行上下文,需要转换】
Filter销售量 = Calculate([销售量],Filter(‘店铺表’,[销售量]>200)) 正确
Filter销售量 = Calculate([销售量],Filter(‘店铺表’,Sum(‘销售表’[销售数量])>200)) 错误
Filter销售量 = Calculate([销售量],Filter(‘店铺表’,Calculate(Sum(‘销售表’[销售数量]))>200)) 正确
为什么[销售量]就不用套Calculate?
因为所有的度量值都自带Calculate函数的功能
销售量 = Sum(‘销售表’[销售数量]) 等同于 销售量 = Calculate(Sum(‘销售表’[销售数量]))
度量值:
总销量 = SUM(‘表’[销售数量])
新建列:
总销量1 = SUM(‘表’[销售数量])
总销量2 = CALCULATE(SUM(‘表’[销售数量]))
所谓的筛选上下文,就是度量值,度量值自带天然的Calculate函数
没有筛选功能的新建列就是行上下文,如果行上下文想转成筛选上下文,它是不会自动转的,你要手动套上一个Calculate函数
度量值:
最大值 = max(‘案例’[成绩]
新建表:
汇总表筛选 = SUMMARIZECOLUMNS(‘案例’[年份],‘案例’[姓名], Filter(‘案例’,‘案例’[科目]=“数学”),“数学”,CALCULATE(max(‘案例’[成绩])))
汇总表筛选 = SUMMARIZECOLUMNS(‘案例’[年份],‘案例’[姓名], Filter(‘案例’,‘案例’[科目]=“数学”),“数学”,[最大值]))
从来就没有什么上下文,都是软件开发者给它起的名字,你且记住:要筛选就用度量值,或者 calculate(聚合函数) ,因为度量值天生就带calculate
06.从来就没有什么上下文
2020年6月26日 22:42
分区 重制版 的第 8 页
使用第06课的课件【表】
总金额 = SUM(‘表’[销售金额])
总成本 = SUM(‘表’[销售成本])
新建列:毛利2 = SUMX(‘表’,[销售金额]-[销售成本])
新建列:毛利2 = CALCULATE(SUMX(‘表’,[销售金额]-[销售成本]))
07.当神秘的x系列遇到了Earlier函数
2020年6月27日 21:39
分区 重制版 的第 9 页
语法:DIVIDE(分子,分母,[替换结果])
替换结果可以省略不写,省略时返回为空。
If(西瓜坏了,换货,[退货])
第三参数可以省略不写,省略时返回为空。
If适合条件比较少的时候使用,如果条件太多,嵌套太多,不方
便使用
称呼 = IF([性别]=“男”,“先生”,“女士”)
switch(表达式,值1,结果1,…,[else])
最后一个参数可以省略不写,省略时返回为空。
月份 = SWITCH(‘switch函数’[月],
1, “一月”, 2,“二月”,
3, “三月”, 4,“四月”,
5, “五月”, 6,“六月”,
7, “七月”, 8,“八月”,
9, “九月”, 10,“十月”,
11,“十一月”, 12,“十二月”,
“未能识别”)
08.安全除法与逻辑判断
2020年6月27日 22:48
分区 重制版 的第 10 页
迪卡尔积:CROSSJOIN(表1,表2)
09.不请自来和迪卡尔积
2020年6月27日 22:51
分区 重制版 的第 11 页
一、代替当前行函数 【不推荐】
出现次数 = countrows(
&&
filter(‘表’,‘表’[姓名] = EARLIER(‘表’[姓名])
‘表’[序号]<=EARLIER(‘表’[序号])))
出现次数2 =
VAR myname = ‘表’[姓名]
VAR index = ‘表’[序号]
return
COUNTROWS(filter(‘表’,‘表’[姓名]=myname && ‘表’[序号]<=index))
VAR函数工作过程是先识别当前行中的姓名和序号,并记录下来结果,然后在return中引用,与相前行效果相同
二、书写更简洁
评价 =
VAR zongfen = ‘表1’[数学] + ‘表1’[语文] + ‘表1’[英语]
return
if (zongfen >=270,“优秀”,“一般”)
注意变量名用英语或汉语拼音
三、不理解上下文也可以用
Filter销售量 = Calculate([销售量],Filter(‘店铺表’,[销售量]>200)) 正确
Filter销售量 = Calculate([销售量],Filter(‘店铺表’,Sum(‘销售表’[销售数量])>200)) 错误
Filter销售量 = Calculate([销售量],Filter(‘店铺表’,Calculate(Sum(‘销售表’[销售数量]))>200)) 正确
如果使用VAR
VAR xsl = Sum(‘销售表’[销售数量])
return
Calculate([销售量],Filter(‘店铺表’,xsl>200))
10.起名大师VAR
2020年6月28日 21:05
分区 重制版 的第 12 页
返回由一列构成的一个表,该表包含来自指定表或列的非重复值。表中重复值将被删除,返回唯一值。
Values函数生成的表是一张虚拟表。
在很多时候使用Filter、Calculate、Countrows、SumX、TopN这些函数,需要引用表而不能直接引用列。Values函数的功能是把列转化成包含
列的表,这时只需要嵌套一个Values函数就可以引用了。
注意:这张表,是不重复的信息表!
假设我没有多张表的关系连接,所有数据都在销售表中:
在新建表那里生成一张虚拟表,从销售数据表中拿到的不重复门店,这样就可以统计销售量大于300的门店销售量了
经典语句: Calculate([度量值],Filter(Values(‘表’[列名]),…))
DISTINCT(表名[字段名]) 返回:去重后,唯一值的列
DISTINCT(表名) 返回:只包含非重复行的表
VALUES 返回一列的唯一值列表, DISTINCT同样是该功能,那么二者有什么不同呢?
一个区别是对于空白行的处理上,VALUES包括没有匹配的空白行。但是DISTINCT不返回没有匹配的空白行。
另外的区别是DISTINCT函数允许列名或任何有效的表表达式作为其参数,但VALUES函数仅接受列名或表名作为参数。
11.你是我的唯一Values和DISTINCT
2020年6月29日 2:59
分区 重制版 的第 13 页
ALL函数的功能:清除筛选条件
ALL函数的应用:清除表中所有行的筛选器以及创建针对表中所有行的计算非常有用。
ALL函数的返回值:已清除筛选的表或列
注意事项:ALL 函数的参数必须是对表的引用或对列的引用。 不能将 ALL 函数与表的表达式或列的表达式一起使用。
All销售量1= Calculate([销售总量],All(‘销售表’))
商品种类 散 盒 总计
蔬菜 52730 52730 52730
水果 52730 52730 52730
肉品 52730 52730 52730
水产 52730 52730 52730
总计 52730 52730 52730
All销售量2= Calculate([销售总量],All(‘商品表’[商品种类]))
商品种类 散 盒 总计
蔬菜 31638 21092 52730
水果 31638 21092 52730
肉品 31638 21092 52730
水产 31638 21092 52730
总计 31638 21092 52730
All销售量3= Calculate([销售总量],All(‘商品表’[商品种类],‘商品表’[商品规格]))
All销售量3= Calculate([销售总量],All(‘商品表’),ALL(‘店铺表’))
Allexcept函数:除…之外
语法:Allexcept(表名[字段名4]) 等同于 All(表名[字段名1],表名[字段名2],表名[字段名3])
一般ALL(表名)会在计算占比时使用
占比=[销售总量]/[ALL销售量1]
注:配合切片器使用,可以筛选门店、日期等
注意:ALL函数在引用列的时候,必需与矩阵的行和列在同一张表
假设,你ALL引用的是商品表,但是矩阵行和列来自店铺表就会出错。
必须保证,ALL所清除的筛选列和初始筛选条件中的筛选列是同一张表的同一列。
引用多个列时,必须是同一张表 - 清除筛选计算占比三个ALL
2020年5月4日 1:57
分区 重制版 的第 14 页
商品种类 商品种类 占比1
蔬菜 蔬菜 36.21%
水果 水果 37.15%
肉品 肉品 17.80%
水产 总计 91.16%
占比1=[销售总量]/[ALL销售量1]
All销售量1= Calculate([销售总量],All(‘销售表’))
如果想让这里显示100%,使用Allselceted函数
Allselceted销售量= Calculate([销售总量],Allselceted(‘销售表’))
占比2=[销售总量]/[Allselceted销售量]
商品种类 商品种类 占比1 占比2
蔬菜 蔬菜 36.21% 39.72%
水果 水果 37.15% 41.58%
肉品 肉品 17.80% 18.70%
水产 总计 91.16% 100.00%
总结:
1、当ALL参数为表时,忽略所有的筛选条件,无论是该图表内还是外部切片器
2、当ALL参数为列时,忽略该列筛选,其它图表字段或外部筛选对基产生作用
3、当ALLselected参数为表时,忽略该图表的筛选条件,其它外部筛选对其产生作用
4、当ALLselected参数为列时,忽略该图表在该列的筛选条件,其它该图表字段或外部筛选对其它产生作用。
12.1 Allselceted函数 【占比显示100%】
2020年5月4日 3:18
分区 重制版 的第 15 页
总销量 = SUM(‘表’[销量] )
ALL表 = CALCULATE([总销量],ALL(‘表’))
占比 = [总销量]/[ALL表]
Allselceted销量 = Calculate([总销量],ALLSELECTED(‘表’))
占比2 = [总销量]/[Allselceted销量]
ALL列 = CALCULATE([总销量],ALL(‘表’[包装规格]))
占比3 = [总销量]/[ALL列]
矩阵:行【商品名称】,列【包装规格】,值【占比2】
比如:苹果是一个单位100%,盒占54%,散占46%
注意:引用列的时候,必需是同一张表
代码:
2020年6月28日 22:31
分区 重制版 的第 16 页
总销量 = SUM(‘销售表’[销售数量])
filter销量 = CALCULATE([总销量],FILTER(‘门店’,[总销量]>100))
Has销量 = IF(HASONEVALUE(‘销售表’[日期].[日]),[filter销
量],BLANK() ) - 只有一个值H与S
2020年6月30日 9:09
分区 重制版 的第 17 页
总销量 = SUM(‘销售表’[销售数量])
filter销量 = CALCULATE([总销量],FILTER(‘门店’,[总销量]>100))
Has销量 = IF(HASONEVALUE(‘销售表’[日期].[日]),[filter销量],BLANK() )
SEL销量 = SELECTEDVALUE (‘表名’[列名])
代码:
2020年6月30日 10:33
分区 重制版 的第 18 页
新建列
单品销售排名 = RANKX(FILTER(‘表’,‘表’[品种]=EARLIER(‘表’[品种])),‘表’[销售金额])
度量值
排名 = RANKX(ALL(‘表’),[总金额],DESC)
RANKX 适合计算明细级别的排序数据
14.武林要以和为贵小伙子你不讲武德
2020年6月30日 18:21
分区 重制版 的第 19 页
TOPN 则可以批量返回结果,从一张表中返回所有满足条件的前 N 行记录。
总金额2 = SUM(‘表2’[销售金额])
前3名 = CALCULATE([总金额2],TOPN(3,VALUES(‘表2’[店号]),[总金额2],1))
占比 = [前3名]/[总金额2]
14.1 TopN
2020年6月30日 18:58
分区 重制版 的第 20 页
一、DATE 返回日期时间格式的日期值,注意:当年份在 0-99 之间,DATE 返回的年份会此在基础上自动加上 1900。超过 99,直接将值用作年份
DATE ( , , )
日期 = DATE([年],[月],[日])
二、对时间元素的提取
年 YEAR
月 MONTH
日
季度
DAY
QUARTER
小时 HOUR
分钟 MINUTE
秒 SECOND
三、返回按指定月数平移后的日期
EDATE ( 日期, 向前或向后月份 ) # 使用正数向后,使用负数向前
例如:2019年12月9日向后一个月是2020年1月9日
四、返回指定月数平移后的月份的最后一天
EOMONTH ( 日期, 向前或向后月份 )
例如:2019年12月9日向后一个月的最后一天是2020年1月31日
五、TIME 将数字形式的小时、分钟和秒转换为日期时间格式
TIME ( , , )
六、保质期函数
DATEDIFF(起始日期,结束日期,DAY)+1 # 如果计算保质期要加1
当前日期和时间:NOW
当前日期:TODAY
日期中的星期:WEEKDAY([日期],2) #2 以周一为起始
日期在当年内的周:WEEKNUM ( [日期],2 ) #2 以周一为起始
15.虽然不智能但是很有用【日期时间函数】
2020年6月30日 19:59
分区 重制版 的第 21 页
find(查找哪个字符,去哪里查找,从哪一个位置查找,如果找不到返回什么值) 第三和四参数可以省略,返回字符在第几位
search(查找哪个字符,去哪里查找,从哪一个位置查找,如果找不到返回什么值) 第三和四参数可以省略,返回字符在第几位
注意:Find查找字符区分大小写,search不区分大小写,其它功能完全一样。
left从左向右取=left([字段名],取几个字符)
right从右向左取=right([字段名],取几个字符)
mid从中间开始取=mid([字段名],从第几个取,取几个)
len长度=len([字段名])
【新建列】上下装 = if(find(“裤”,‘表’[商品名称],1,999)<999 || find(“裙”,‘表’[商品名称],1,999)<999,“下装”,“上装”)
16.可以不用但是要会 【文本函数】
2020年5月12日 10:02
分区 重制版 的第 22 页
一、检查值是否为空,并返回 TRUE 或 FALSE
ISBLANK ( )
二、检查值是否为错误,并返回 TRUE 或 FALSE
ISERROR ( )
三、检查值是否是逻辑值(TRUE 或 FALSE),并返回 TRUE 或 FALSE
ISLOGICAL ( )
四、检查值是否为非文本(空白单元格不是文本),并返回 TRUE 或 FALSE
ISNONTEXT ( )
五、检查值是否为数字,并返回 TRUE 或 FALSE
ISNUMBER ( )
六、检查值是否为文本,并返回 TRUE 或 FALSE
ISTEXT ( )
七、检查表或表表达式是否为空
ISEMPTY()
附:转换函数
2020年6月30日 19:59
分区 重制版 的第 23 页
17.这回智能真的来了【时间智能函数】
2020年7月1日 9:32
分区 重制版 的第 24 页
日期表 = ADDCOLUMNS(
CALENDAR(date(2019,1,1),date(2019,12,31)),
“年”, YEAR ( [Date] ),
“季度”, ROUNDUP(MONTH([Date])/3,0),
“月”, MONTH([Date]),
“周”, weeknum([Date]),
“年季度”, year([date]) & “Q” & ROUNDUP(MONTH([Date])/3,0),
“年月”, year([Date]) * 100 + MONTH([Date]),
“年周”, year([Date]) * 100 + weeknum([Date]),
“星期几”, WEEKDAY([Date])
)
17.1 日期表
2020年7月1日 9:46
分区 重制版 的第 25 页
年初至今(YTD)、季初至今(QTD)和月初至今(MTD)
累计是指定维度上值的求和,时间的累计就是在时间维度上计算当期值然后求和而来。
比如年累计(MTD)是将当月的值累加求和,下个月的值重新计算。
一、计算本年的年初至今(YTD)、季初至今(QTD)和月初至今(MTD)
销量 = SUM(‘表’[销售])
年初至今 = TOTALYTD([总销量],‘表’[日期])
月初至今 = TOTALMTD([总销量],‘表’[日期])
季出至今 = TOTALQTD([总销量],‘表’[日期])
年初至今 = TOTALYTD([总销量],‘表’[日期],“6-30”) # 从7月1日开始计算为一年
如果您单位从每年3月1日开始计算,那么需要考虑平年和闰年,方法请自行在百度搜索
二、计算去年同期
去年同期年初至今 = TOTALYTD([总销量],SAMEPERIODLASTYEAR(‘表’[日期]))
去年同期月初至今 = TOTALMTD([总销量],SAMEPERIODLASTYEAR(‘表’[日期]))
去年同期季初至今 = TOTALQTD([总销量],SAMEPERIODLASTYEAR(‘表’[日期]))
同比增长(下降)率=(本期数-去年同期数)/去年同期数×100%
环比增长(下降)率=(本期数-上期数)/上期数×100%
去年同期
17.2 计算累加值
2020年7月1日 9:46
分区 重制版 的第 26 页
Datesbetween(‘表’[日期],开始日期,结束日期)
指定时间销量 = CALCULATE([总销量],DATESBETWEEN(‘表’[日期],DATE(2019,4,1),DATE(2020,4,30) ))
17.3 指定时间段的累加值
2020年6月30日 10:29
分区 重制版 的第 27 页
ENDOFMONTH(日期列,年度结束日期) # 返回当月最后一天
STARTOFMONTH(日期列,年度结束日期) # 返回当月第一天
datesinperiod函数:指定开始日期,向前或向后多少天、月、季度、年
语法:datesinperiod(日期列,开始日期,移动间隔,移动粒度)
每月最后3天销量 = CALCULATE([销售量],DATESINPERIOD(‘表2’[日期],ENDOFMONTH(‘表2’[日期]),-3,DAY))
每月前3天销量 = CALCULATE([销售量],DATESINPERIOD(‘表2’[日期],STARTOFMONTH(‘表2’[日期]),3,DAY))
拓展知识:
计算每年1月1日的销量 = CALCULATE([销售量],STARTOFyear(‘表’[日期]))
计算每年12月31日销量 = CALCULATE([销售量],ENDOFyear(‘表’[日期]))
计算指定年度开始前5天的销量
= CALCULATE([销售量],DATESINPERIOD(‘表’[日期],STARTOFMONTH(‘表’[日期],“5-31”),5,DAY))
求最近30天的移动平均
= Calculate([销售量], datesinperiod(‘表’[日期],max(‘表’[日期]),-30,day)/30
切记:你的日期列是唯一值,否则使用Values创建一个唯一值的表或者使用去重方法均可
DAY,month,quarter,year
17.4 一定间隔时间段
2020年6月30日 11:46
分区 重制版 的第 28 页
17.5 日期中的最大值与最小值
2020年6月30日 12:21
分区 重制版 的第 29 页
今年1月1日至今 = TOTALYTD([总金额],‘日期表’[Date])
同期 = CALCULATE([今年1月1日至今],DATEADD(‘日期表’[Date],-1,YEAR))
本月1月1日至今 = TOTALMTD([总金额],‘日期表’[Date])
同期 = CALCULATE([今年1月1日至今],DATEADD(‘日期表’[Date],-1,month))
17.6 Dateadd函数 【同期、环比】
2020年6月30日 12:27
分区 重制版 的第 30 页
18.中文排序
2020年7月2日 4:07
分区 重制版 的第 31 页
1、 到我的评论区置顶链接中下载 : mysql-connector-net-8.0.20.msi
19.连接MySQL
2020年7月2日 4:07
分区 重制版 的第 32 页
分区 重制版 的第 33 页
20.获取数据、向下钻取与筛选
2020年7月2日 4:18
分区 重制版 的第 34 页
微软官方DAX语言查询:中文版
https://docs.microsoft.com/zh-cn/dax/new-dax-functions
powerbi进阶篇 第一季 销售案例
https://www.bilibili.com/video/BV18C4y1H7b9
powerbi进阶篇 第二季 商品案例
https://www.bilibili.com/video/BV14Z4y1p7fY
powerbi进阶篇 第三季 人力与财务案例
https://www.bilibili.com/video/BV1Je411W76y
DAX语言查询及进阶篇观看地址:
2020年7月1日 20:30
分区 重制版 的第 35 页
财务 & 人力篇
盈亏平衡分析是企业结合收入、成本、利润的综合分析方法,它可以判断企业的经营状况,为企业未来的经营方向提供指引
收入 = 总成本+利润 ,盈亏平衡点是收入正好等于总成本的销售金额,通过计算盈亏平衡点能判断销售金额需要达到多少才可以保本
001.盈亏平衡分析
2020年5月3日 22:33
分区 财务与人力篇 的第 1 页
参数名 最小 最大 增量
销售折扣 0.4 1 0.01
进货折扣 0.1 0.6 0.01
销售扣点 0 0.4 0.01
员工提成系数 0.01 0.05 0.01
销售金额(万) 5 100 1
员工数量 1 20 1
月租金(万) 0 100 1
人均固定工资 1000 5000 100
水电费 500 5000 100
其它固定费用 0 10000 100
1.1 新建参数
2020年5月19日 8:48
分区 财务与人力篇 的第 2 页
1.2 新建一个表存放度量值
2020年5月19日 8:48
分区 财务与人力篇 的第 3 页
(1)固定成本是不随销售变化而变化的成本,例如水电费,无论业绩多少,水电费都是固定支出。【注意租金万元换算成元】
固定成本 = [人均固定工资 值]*[员工数量 值] +[水电费 值]+[月租金(万) 值]10000+[其它固定费用 值]
(2)变动成本随着销售变化,例如销售越高,员工资金越多
变动成本 =
[销售额(万) 值]10000/[销售折扣 值][进货折扣 值]+
[销售额(万) 值]10000[员工提成系数 值]+
[销售额(万) 值]10000[商场、平台销售扣点 值]
总成本 = [变动成本]+[固定成本]
1.3 计算成本
2020年5月19日 8:48
分区 财务与人力篇 的第 4 页
销售金额扣除所有成本后就是净利润(本例中暂不考虑税金问题),净利润率即每100元能赚多少钱,净利润率越高代表企业盈利能力越强。
净利润 = [销售额(万) 值]10000-[总成本]
净利润率 = divide([净利润],[销售额(万) 值]10000)
1.4 计算净利润及净利润率
2020年5月19日 9:23
分区 财务与人力篇 的第 5 页
当盈亏平衡时,意味着企业没有利润,即净利润为0,或者销售金额=总成本
销售金额=固定成本+变动成本
销售金额=固定成本+销售扣点金+员工奖金+商品进货成本
销售金额 = 固定成本+销售金额销售扣点+销售金额员工提成系数+销售金额/销售折扣进货折扣
盈亏平衡销售额 = DIVIDE([固定成本],1-[销售扣点 值]-[员工提成系数 值]-DIVIDE([进货折扣 值],[销售折扣 值]))
1.5 计算盈亏平衡销售金额
2020年5月19日 9:27
分区 财务与人力篇 的第 6 页
1.6 可视化
2020年5月19日 9:27
分区 财务与人力篇 的第 7 页
商品毛利率 = DIVIDE([销售折扣 值]-[进货折扣 值],[销售折扣 值])
商品毛利 = [销售额(万) 值]10000[商品毛利率]
1.7 毛利金额与毛利率计算
2020年5月19日 9:45
分区 财务与人力篇 的第 8 页
盈亏平衡在于把握收入,成本和利润之间的关系,将收入,成本和利润构成因子层层细分,将其中可以改变的点做为变量,使用Powerbi参数功能调整变量值,
即可构成模型分析经营上可能做出的提升点
这个模型无论是租金方式还是扣点方式店铺均适用,租金店铺将扣点设置为0。扣点店铺将租金设置为0。
如果是电商,需要两种方式结合,扣点和租金分别设置相应值即可。例如,某猫,收你月租还扣你点
当分析中,涉及变量较多,可以使用思维脑图软件进行分解 ,具体软件可以百度搜索
1.8 总结
2020年5月19日 10:07
分区 财务与人力篇 的第 9 页
利润总额=经营利润-非可控费用
经营利润=毛利额(不含税)-税金及附加+其他业务收入(不含税)+营业外收支-可控费用
002.利润表分析
2020年5月15日 11:42
分区 财务与人力篇 的第 10 页
由于不同店铺数据存放在不同的工作簿中,且格式相同,因此可
以使用导入文件夹的方式导入数据。
(1)数据导入
按文件夹
(2)只保留Content与Name两列,其它删除
(3)添加自定义列
(4)筛选Data,不要前缀,展开,删除Content列
(5)字段名重命名,店名把.xlsx替换为空
2.1 通过文件夹导入数据
2020年5月19日 11:36
分区 财务与人力篇 的第 11 页
复制一份
删除重复项
重命名:科目
2.2 科目排序
2020年5月20日 0:40
分区 财务与人力篇 的第 12 页
选中店名,科目
转换 - 逆透视其它列
金额改成小数
2.3 逆透视数据列
2020年5月20日 0:40
分区 财务与人力篇 的第 13 页
因为顺序乱了
2.4 合并查询引用索引
2020年5月20日 0:40
分区 财务与人力篇 的第 14 页
对科目表取消启用加载,关闭并应用
分区 财务与人力篇 的第 15 页
开始-输入数据
2.5 切片器
2020年5月20日 1:24
分区 财务与人力篇 的第 16 页
显示金额 = IF(HASONEVALUE(‘单位’[显示单位]),DIVIDE(SUM(‘02 利润表分析’[金额]),VALUES(‘单位’[分母])),SUM(‘02 利润表分析’[金额]))
当显示单位为唯一值时(切片器选中一个单位)。金额与显示单位中的分母相除
当切片器末选中任何值时,按照原金额显示(即单为元)
2.6 金额与显示单位绑定,建立显示金额度量值
2020年5月20日 1:35
分区 财务与人力篇 的第 17 页
行:科目 (按索引排序)
列:年
值:显示金额
2.7 矩阵表
2020年5月20日 1:38
分区 财务与人力篇 的第 18 页
一、做两个切片器
一个店名,一个年
二、基础度量值,使用calculate加条件的方法
营收 = CALCULATE ( [显示金额], ‘利润表’[科目] = “一、营业总收入” )
营业总成本 = CALCULATE ( [显示金额], ‘利润表’[科目] = “二、营业总成本” )
营业成本 = CALCULATE ( [显示金额], ‘利润表’[科目] = “营业成本” )
销售费用 = CALCULATE ( [显示金额], ‘利润表’[科目] = “销售费用” )
管理费用 = CALCULATE ( [显示金额], ‘利润表’[科目] = “管理费用” )
净利润 = CALCULATE ( [显示金额], ‘利润表’[科目] = “五、净利润” )
三、设置率值相关度量值
不同店铺由于规模结构不同,成本,费用等指标直接比较没有实际意义,换算成率值后才可以进行有效分析。【比如1万平米的店铺和1000平米的店铺】
营业成本率 = DIVIDE([营业成本],[营收])
销售费用率 = DIVIDE([销售费用],[营收])
管理费用率 = DIVIDE([管理费用],[营收])
净利润率 = DIVIDE([净利润],[营收])
四、计算同比
去年金额 =
VAR LastYear =
SELECTEDVALUE( ‘利润表’[年] ) - 1
RETURN
CALCULATE ( [显示金额], ‘利润表’[年] = LastYear )
这里我没有用时间智能函数,因为不具备标准的时间格式,如果你想用时间智能函数,就将年份字段变更为当年的任意某天日期,然后新建日期表进行计算。
同比 = if(SELECTEDVALUE(‘利润表’[年])>2010,DIVIDE([显示金额]-[去年金额],[去年金额]))
因为2010年为最小年份,没有同比数据,因此使用if语句进行剔除
五、同比修正
我们可以增加条件,对负值进行特殊处理,当本期净利润为负值、去年同期利润为正值时,显示L,表示由盈利转亏损
当本期净利润为正值,去年同期为负值时,显示P,表示 本期扭亏为盈
同比_修正 =
IF (
SELECTEDVALUE( ‘利润表’[年] ) > 2008,
IF (
[显示金额] > 0
&& [去年金额] < 0,
“P”,
IF ( [显示金额] < 0 && [去年金额] > 0, “L”, DIVIDE ( [显示金额] - [去年金额], [去年金额] ) )
),
BLANK ()
)
003.利润表分析
2020年5月19日 19:32
分区 财务与人力篇 的第 19 页
3.1 可视化
2020年5月19日 19:40
分区 财务与人力篇 的第 20 页
3.2 簇状柱形图
2020年5月19日 19:41
分区 财务与人力篇 的第 21 页
3.2 折线图
2020年5月19日 19:41
分区 财务与人力篇 的第 22 页
3.4 矩阵
2020年5月19日 19:52
分区 财务与人力篇 的第 23 页
004.人员结构分析
2020年5月19日 10:10
分区 财务与人力篇 的第 24 页
【新建表】日期表 = CALENDARAUTO()
【新建列】
年 = YEAR(‘日期表’[日期])
月 = MONTH(‘日期表’[日期])
季度 = “Q”&FORMAT(‘日期表’[日期],“Q”)
【度量值】
期末在职人数 =
CALCULATE (
DISTINCTCOUNT ( ‘员工信息’[员工工号] ),
FILTER (
FILTER ( ‘员工信息’, ‘员工信息’[入职日期] <= MAX ( ‘日期表’[日期] ) ),
‘员工信息’[离职日期] > MAX ( ‘日期表’[日期] )
|| ‘员工信息’[离职日期] = BLANK ()
) )
注:max用来判断切片器选定的截止日期。内部的Filter限定范围为所有入职日期不大于当前切片日期,外层Filter对上一层filter选取的范围再次进行缩
小,保留离职日期大于当前最大日期的人员(在当前切片日期条件下,该员工仍然在职)及没有离职的人员,剩余的人员即截至当前日期的所有在职人员。
该期间新入职人数 =
CALCULATE (
DISTINCTCOUNT ( ‘员工信息’[员工工号] ),
FILTER (
FILTER (
‘员工信息’,
‘员工信息’[入职日期] <= MAX ( ‘日期表’[日期] )
&& ‘员工信息’[入职日期] >= MIN ( ‘日期表’[日期] )
),
‘员工信息’[离职日期] > MAX ( ‘日期表’[日期] )
|| ‘员工信息’[离职日期] = BLANK ()
))
年龄 = MAX(‘日期表’[年])-MAX(‘员工信息’[出生年份])
30岁以下员工占比 =
VAR SN_Under30 =
CALCULATE ( [期末在职人数], FILTER ( ALLSELECTED ( ‘员工信息’[员工工号] ), [年龄] < 30 ) )
VAR SN_All =
CALCULATE ( [期末在职人数], ALLSELECTED ( ‘员工信息’[员工工号] ) )
RETURN
DIVIDE ( SN_Under30, SN_All )
准备工作
2020年5月19日 20:53
分区 财务与人力篇 的第 25 页
4.1 离职人数
2020年5月20日 9:36
分区 财务与人力篇 的第 26 页
4.2 簇状条形图
2020年5月20日 9:36
分区 财务与人力篇 的第 27 页
4.3 饼图
2020年5月20日 9:36
分区 财务与人力篇 的第 28 页
4.4 簇状柱形图
2020年5月20日 9:39
分区 财务与人力篇 的第 29 页
离职率 = 期间离职人数/(期间离职人数+期末在职人数)
005.离职率分析
2020年5月20日 9:35
分区 财务与人力篇 的第 30 页
期间离职人数 =
CALCULATE (
DISTINCTCOUNT ( ‘员工信息’[员工工号] ),
FILTER (
FILTER ( ‘员工信息’, ‘员工信息’[入职日期] <= MAX ( ‘日期表’[日期] ) ),
‘员工信息’[离职日期] <= MAX ( ‘日期表’[日期] )
&& ‘员工信息’[离职日期]>=MIN(‘日期表’[日期])
) )
离职率 = DIVIDE([期间离职人数],[期末在职人数]+[期间离职人数])
5.1 准备工作
2020年5月19日 22:20
分区 财务与人力篇 的第 31 页
5.2 可视化
2020年5月19日 22:21
分区 财务与人力篇 的第 32 页
5.2.1 饼图
2020年5月19日 22:21
分区 财务与人力篇 的第 33 页
5.2.2 簇状柱形图
2020年5月19日 22:22
分区 财务与人力篇 的第 34 页
培训记录:
(1)将null替换成0
(2)选中工号,日期,姓名三列后,逆透视其它列
(3)修改字段名:课程名称,分数
(4)关闭并应用
006.新员工分析
2020年5月19日 23:20
分区 财务与人力篇 的第 35 页
(1)计算年龄:用现在的年份减去员工出生那一年的年份
年龄 = YEAR(TODAY())-YEAR(‘员工信息’[出生日期])
(2)计算年龄段:
年龄段 = SWITCH(
TRUE(),
‘员工信息’[年龄]<30,“30岁以下”,
‘员工信息’[年龄]<40,“30-40岁”,
‘员工信息’[年龄]<50,“40-50岁”,
“50岁以上”
)
6.1 计算年龄与年龄段【新建列】
2020年5月21日 7:52
分区 财务与人力篇 的第 36 页
(1)计算工龄:
工龄 = IF(‘员工信息’[离职日期]>0,
ROUND((‘员工信息’[离职日期]-‘员工信息’[入职日期])/365,1),
ROUND((TODAY()-‘员工信息’[入职日期])/365,1)
)
如果离职日期是空,那就是0,证明在职,如果有离职日期,那么日期是一个数值,所以有离职日期的,用离职日期减入职日期除以365,保留1位小数点。否则
今天的日期减入职日期除365天,保留一位小数点。
(2)计算工龄段:
工龄段 = SWITCH(
true(),
‘员工信息’[工龄]<0.5,“半年以下”,
‘员工信息’[工龄]<1,“0.5-1年”,
‘员工信息’[工龄]❤️,“3年以上”,
“3年以上”
)在DaX函数中,日期之间可以直接进行加减计算,但在Excel Query中使用M函数无法实现同样的效果,需要首先将日期转为数值。
6.2 计算工龄与工龄段【新建列】
2020年5月21日 7:52
分区 财务与人力篇 的第 37 页
考核结果 = IF(‘培训记录’[分数]>=60,“通过”,IF(‘培训记录’[分数]>0,“不及格”,“未提交答卷”))
6.3 计算培训考核结果【新建列】
2020年5月21日 7:52
分区 财务与人力篇 的第 38 页
在职员工数量 = CALCULATE(DISTINCTCOUNT(‘员工信息’[员工工号]),‘员工信息’[员工状态]=“在职”)
新员工数量 = CALCULATE([在职员工数量],‘员工信息’[工龄段]=“半年以下”)
新员工比例 = DIVIDE([新员工数量],[在职员工数量])
6.4 计算在职员工数,新员工数,新员工比例 【度量值】
2020年5月22日 9:49
分区 财务与人力篇 的第 39 页
通过课程数量 = COUNTROWS(FILTER(RELATEDTABLE(‘培训记录’),‘培训记录’[考核结果]=“通过”))
在数据关系中,员工信息表是“一端”,培训记录是“多端”,将多端数据引入一端需要借助Relatedtable,但是Relatedtable返回的是表,无法直接
显示,因为countrows对行数进行计数,又因为我们需要的不是全部行,而是考试通过的行,所以Filter增加条件对符合“通过”条件的行进行筛选
6.5 计算每位员工通过课程数量【员工信息表新建列】
2020年5月22日 9:54
分区 财务与人力篇 的第 40 页
新员工入职考试通过人数 = CALCULATE([新员工数量],‘员工信息’[通过课程数量]=5)
6.6 计算新员工入职考试通过课程数量【度量值】
2020年5月22日 9:59
分区 财务与人力篇 的第 41 页
1、切片器-部门
2、卡片图:在职员工数量、新员工数量、新员工比例
3、环形图:
(1)图例:年龄段,值:新员工数量
(2)图例:文化程度,值:新员工数量
4、簇状柱形图:轴-部门,值-新员工数量,新职工入职考试通过人数
5、百分比堆积条形图:部门,工龄段,在职员工数
6.7 可视化图表
2020年5月22日 10:06
分区 财务与人力篇 的第 42 页
007. 新员工成绩分析
2020年5月22日 10:14
分区 财务与人力篇 的第 43 页
请详见视频
7.1 添加切片器和图表
2020年5月21日 22:28
分区 财务与人力篇 的第 44 页
Chicletslicer切片器 - 员工工号
页眉-关
多重选择-关
强制选择-开
筛选 在职 和 新员工
7.2 切片器
2020年5月21日 22:28
分区 财务与人力篇 的第 45 页
姓名,出生日期,入职日期,文化程度
性别,所在部门,职称,职级
注意:类别标签(关),标题(开)
7.3 卡片图
2020年5月21日 22:28
分区 财务与人力篇 的第 46 页
培训信息提示 =
VAR a=SUM(‘员工信息’[通过课程数量])
RETURN
IF(a=5,“该员工入职考试已全部通过”,“该员工尚有”& 5-a &“门课程未通过”)
7.4 培训信息提示
2020年5月21日 22:28
分区 财务与人力篇 的第 47 页
新建列:
合并 = ‘培训记录’[课程名称]&" "&‘培训记录’[得分]&“分”
及格线 = 60
7.5 雷达图
2020年5月21日 22:40
分区 财务与人力篇 的第 48 页
预测可能发生的事情
1 忘记自己打没打卡,重复打了多次卡
2 漏打卡了
步骤:
1 天 = Day(‘考勤表’[时间])
2 第一次打卡时间=calculate(firstnonblank(‘考勤表’[时间],1),ALLEXCEPT(‘考勤表’,‘考勤表’[姓名],‘考勤表’[天]))
3 最后一次打卡时间=calculate(lastnonblank(‘考勤表’[时间],1),ALLEXCEPT(‘考勤表’,‘考勤表’[姓名],‘考勤表’[天]))
4 到报表区建一个表
5 漏卡=if(‘考勤表’[第一次打卡时间]=‘考勤表’[最后一次打卡时间],“漏卡”)
6 加班=if(HOUR(‘考勤表’[最后一次打卡时间])>=18,“加班”)
7 迟到=if(HOUR(‘考勤表’[第一次打卡时间])>=8 && MINUTE(‘考勤表’[第一次打卡时间])>=30,“迟到”)
8 迟到时间temp = (HOUR(‘考勤表’[第一次打卡时间])-8)6060+(Minute(‘考勤表’[第一次打卡时间])-30)*60+second(‘考勤表’[第一次打卡时间])
9 迟到时间 = IF([迟到时间temp]>0,[迟到时间temp])
008.考勤整理
2020年5月22日 0:54
分区 财务与人力篇 的第 49 页
在进行员工考勤分析之前,首先我们要先思考一个本质的问题:为什么要考勤?考勤得来的数据可以反映出哪些有价值的内容?通过考勤我们
可以知道公司员工最基本的工作情况:出勤、缺勤率,加班情况;通过考勤我们也可以知道哪些人(岗位)比较忙,哪些人(岗位)比较闲,
通过考勤数据分析有助于我们进行岗位设置、定员定编的调整;甚至当我们发现某个团队集体忙碌的时候,那很有可能团队有大的订单拿,当
某个团队集体清闲的时候,可能在未来的一段时间内都没有订单可拿;最后,通过数据分析我们也可以知道一个人经常缺勤,迟到早退,他可
能已经回有离职的倾向了……
所以呢,考勤分析可以从:出勤率、缺勤率、迟到、早退、加班等横向指标和部门、团队、员工(岗位)等纵向指标来分析。
考勤数据不仅可作为发放工资依据,还可以深入分析发现异常状况,提前做出沟通与预警。某个公司2020年5月全月的考勤数据,从全勤和工
作时长的视角分析公司全勤具体情况。全勤是指在指定时间按照公司规定的上班天数上班,没有迟到或早退等现象。该公司有三种考勤方式:
1、 职位经理级别的管理层,采用的是弹性上班制度,无须打卡,每周一到周五上班。
2、 非销售岗员工上班时间固定,每天工作8小时,从早上9点到下午17点为上班时间段,每周一到周五上班。
迟到:9点以后打卡
早退:17点之前打卡
旷工:未打卡
3、销售岗位 采用非固定上班时间制度,每天工作6个小时,每个月可以排班休息5天,上下班打卡时间少于6小时算迟到/早退,未打卡算旷
工。
除周末,节假日等必要条件外,该公司员工享有一定天数的请假福利。本文将以三种考勤方式为基础,分析公司员工的全勤状况。弄明白了考
勤规则接下来我们开始正式制作PowerBI图表。
009.全勤分析
2020年5月22日 0:51
分区 财务与人力篇 的第 50 页
星期 = WEEKDAY(‘考勤’[日期],2)
日期分类 = IF(‘考勤’[星期]>5,“双休日”,“工作日”)
9.1 添加星期和日期【新建列】
2020年5月22日 1:22
分区 财务与人力篇 的第 51 页
时间差 = DATEDIFF(‘考勤’[下班打卡时间],[上班打卡时
间],MINUTE)
工时 = DIVIDE([时间差],60)
9.2 计算每个人每天上班的工时【新建列】
2020年5月22日 1:22
分区 财务与人力篇 的第 52 页
考勤状态 =
IF (
‘考勤’[请假状态] = BLANK (),
SWITCH (
‘考勤’[考勤方式],
“弹性”, IF(‘考勤’[日期分类]=“工作日”,“正常”,BLANK()),
“月休5天”, IF ( ‘考勤’[上班打卡时间] >= 6, “正常”, IF ( ‘考
勤’[上班打卡时间] >0, “迟到早退”, BLANK() ) ),
“双休”, IF (
‘考勤’[日期分类] = “工作日”,
IF (
‘考勤’[上班小时] = 0,
“旷工”,
IF (
‘考勤’[上班打卡时间] > TIME ( 9, 0, 0 )
|| ‘考勤’[下班打卡时间] < TIME ( 17, 0, 0 ),
“迟到早退”,
“正常”
)
),
BLANK ()
)
),
BLANK()
)
9.3 判断考勤状态【新建列】
2020年5月22日 1:22
分区 财务与人力篇 的第 53 页
当月天数 = DISTINCTCOUNT(‘考勤’[日期])
工作日数量 = CALCULATE(DISTINCTCOUNT(‘考勤’[日期]),‘考勤’[日期分类]=“工作日”)
9.4 当月天数与工作日数量的度量值
2020年5月22日 4:01
分区 财务与人力篇 的第 54 页
考勤汇总 = SUMMARIZECOLUMNS(
‘考勤’[员工工号],
‘考勤’[姓名],
‘考勤’[考勤方式],
“正常出勤天数”, COUNTROWS ( FILTER ( ‘考勤’, ‘考勤’[考勤状态] = “正常” ) ),
“迟到早退天数”, COUNTROWS ( FILTER ( ‘考勤’, ‘考勤’[考勤状态] = “迟到早退” ) ),
“旷工天数”, COUNTROWS ( FILTER ( ‘考勤’, ‘考勤’[考勤状态] = “旷工” ) ),
“请假天数”, COUNTROWS ( FILTER ( ‘考勤’, ‘考勤’[请假状态] <> BLANK () ) ),
“工作时长”,SUM(‘考勤’[上班打卡时间])
)
9.5 考勤汇总【新建表】
2020年5月22日 4:08
分区 财务与人力篇 的第 55 页
9.6 建立关系连线
2020年5月22日 4:09
分区 财务与人力篇 的第 56 页
应出勤天数 = if(‘考勤汇总’[考勤方式]=“月休5天”,[当月天数]-5,[工作日数量])
9.7 计算每个人应该出勤的天数
2020年5月22日 4:11
分区 财务与人力篇 的第 57 页
通过函数SUMMARIZEcloums计算了旷工天数,但是这个天数对于销售人员来说是错误的,因为销售人员应该上班天数是26天,但是正常出勤天数只有25天,
旷工天数列并没有显示异常
因此我们需要对旷工天数进行调整
旷工天数2 =
VAR a = ‘考勤汇总’[应出勤天数] - ‘考勤汇总’[正常出勤天数]
- ‘考勤汇总’[请假天数]
- ‘考勤汇总’[迟到早退天数]
RETURN
IF ( ‘考勤汇总’[考勤方式] <> “月休5天”, ‘考勤汇总’[旷工天数], IF ( a > 0, KG, BLANK () ) )
当考勤方式不是【月休5天】时返回原旷工天数值;否则通过应出勤天数-正常出勤天数-请假天数-迟到/早退天数=旷工天数。
9.8 计算旷工天数
2020年5月22日 4:12
分区 财务与人力篇 的第 58 页
员工数量 = CALCULATE(DISTINCTCOUNT(‘花名册’[员工工号]),‘花名册’[员工状态]=“在职”)
全勤人数 = CALCULATE([员工数量],FILTER(‘考勤汇总’,‘考勤汇总’[正常出勤天数]>=‘考勤汇总’[应出勤天数]))
旷工人数 = CALCULATE([员工数量],FILTER(‘考勤汇总’,‘考勤汇总’[旷工天数2]>0))
请假人数 = CALCULATE([员工数量],FILTER(‘考勤汇总’,‘考勤汇总’[请假天数]>0))
迟到早退人数 = CALCULATE([员工数量],FILTER(‘考勤汇总’,‘考勤汇总’[迟到早退天数]>0))
9.10 度量值
2020年5月22日 4:35
分区 财务与人力篇 的第 59 页
卡片图:员工数量,全勤人数,全勤率,旷工人数,迟到早退人
数
考勤人数 = DISTINCTCOUNT(‘考勤汇总’[员工工号])
全勤人数 = CALCULATE([考勤人数],FILTER(‘考勤汇总’,‘考勤汇
总’[正常出勤天数]>=‘考勤汇总’[应出勤天数]))
全勤率 = DIVIDE([全勤人数],[员工数量])
旷工人数 = CALCULATE([考勤人数],FILTER(‘考勤汇总’,‘考勤汇
总’[旷工天数2]>0))
迟到早退人数 = CALCULATE([考勤人数],FILTER(‘考勤汇总’,‘考
勤汇总’[迟到早退天数]>0))
请假人数 = CALCULATE([考勤人数],FILTER(‘考勤汇总’,‘考勤汇
总’[请假天数]>0))
9.11 可视化-卡片图
2020年5月22日 4:47
分区 财务与人力篇 的第 60 页
9.12 可视化-折线和簇状柱形图
2020年5月22日 5:05
分区 财务与人力篇 的第 61 页
9.13 可视化- 簇状条形图
2020年5月22日 5:05
分区 财务与人力篇 的第 62 页
9.14 可视化-表
2020年5月22日 5:07
分区 财务与人力篇 的第 63 页
人均工作时长 = DIVIDE(SUM(‘考勤汇总’[工作时长]),DISTINCTCOUNT(‘考勤汇总’[员工工号]))
簇状条形图
簇状柱形图
010.工作时长分析
2020年5月19日 23:21
分区 财务与人力篇 的第 64 页
分区 财务与人力篇 的第 65 页
自定义图表
01.阿斯特图(Aster plot)
2020年8月13日 2:01
分区 自定义图表 的第 1 页
详见视频
如果单词不认识可以到百度查或有道翻页查询
02.博彦日历(Beyondsoft Calendar)
2020年8月15日 19:22
分区 自定义图表 的第 2 页
子弹图可视化主要用于考核实际数据与目标的差异情况,在各种KPI分析中经常可以看到,比如预实分析或考核销售达成
情况等,因其外形像一个射出的子弹而得名。
子弹图也可以当做KPI使用
03.子弹图(Bullet Chart)
2020年8月16日 0:13
分区 自定义图表 的第 3 页
(1)表示两两之间的数量关系,比如A地到B地的人流量,每个地区不同季度下的销售额。第一列和第二列最好是同一类数据
(比如同为文本型数据),第三列是数值(比如表示为两者间的强度)
(2)注意点
①弦图是一种显示矩阵中数据之间相互关系的图形方法。
②互连线条的宽度取决于相应的值。
③节点的颜色可以在视觉的格式设置中更改。
④单击源节点或目标节点将按所选语句筛选其他视觉效果。
(3)Powerbi运用
只需要选择Chord图,再勾选三列数据即可。数值可以是度量值
04.和弦图(Chord)
2020年8月16日 8:25
分区 自定义图表 的第 4 页
任务完成率 = [实标销售]/[销售任务]
KPI指标 =
SWITCH(TRUE(),
[任务完成率]>=1.3,1,
[任务完成率]<1.1,3,
2)
05.PowerKPI
2020年8月16日 8:50
分区 自定义图表 的第 5 页
5.1 设置
2020年8月16日 10:14
分区 自定义图表 的第 6 页
06.甘特图(Gantt)
2020年8月16日 11:33
分区 自定义图表 的第 7 页
直方图,又称质量分布图,是一种统计报告图
了解详细情况,可以参考百度百科
我们可以应用到自己的工作场景中,比如客单价出现的频率
07.直方图(Histogram Chart)
2020年8月16日 13:42
分区 自定义图表 的第 8 页
带有动画点的动画拆线图
这里调整排序
08.点线图(LineDot Chart)
2020年8月16日 14:01
分区 自定义图表 的第 9 页
09.脉冲图(pulse chart)
2020年8月16日 14:32
分区 自定义图表 的第 10 页
注意:
加一个合格线
10.雷达图(Radar Chart)
2020年8月16日 15:31
分区 自定义图表 的第 11 页
桑基图(Sankey diagram),即桑基能量分流图,也叫桑基能量平衡图。它是一种特定类型的流程图,图中
延伸的分支的宽度对应数据流量的大小,通常应用于能源、材料成分、金融等数据的可视化分析。因1898年
Matthew Henry Phineas Riall Sankey绘制的“蒸汽机的能源效率图”而闻名,此后便以其名字命名为“桑
基图”。
一般是多对多的关系时使用
11.桑基图(Sankey Chart)
2020年8月17日 9:32
分区 自定义图表 的第 12 页
河流图看起来很优雅,也很吸引眼球,但使用时应该注意的是,它无法精准的表达数据,流线的凸起和下凹也并不代表上升和下降。
它更适合展示大量数据的波动情况,尤其是某些类别大幅波动时,利用河流图可以很直观的展现出来。
12.流线图&河流图【Stream Graph】
2020年8月17日 9:58
分区 自定义图表 的第 13 页
展示不同分类的占比情况时,我们经常用到的是饼图,但遇到多层次的数据时,饼图就显得力不从心,不过还有个更强大的"饼图"可以帮助你:旭日图。
旭日图常用于分析销售的分布情况,假设按时间维度(年度、季度),以及产品维度(产品类别和产品名称)来分别展示销售额,把相关字段拖进去,
一个旭日图就出来了,
每一个扇形可以设置圆角以及扇形之间的间隙,比如在Configuration中设置为这样的参数:
13.旭日图&阳光图【Sunburst by MAQ
Software】
2020年8月17日 10:25
分区 自定义图表 的第 14 页
当你将鼠标悬停在该图表上时,右上角出现一个“ Edit Mark”按钮,点击进入,就是Infographic Designer的设计器,会看到有很多的设置,在
这里你可以控制该图表的各种元素。可以更改柱形图的形状
(1)可以从电脑导入图片
(2)每个形状还可以切割为M*N的小方格
(3)每个门店还可以单独自定义。
(4)颜色也可以分别自定义设置
14.百变星君图 【Infographic Designer】
2020年8月16日 21:14
分区 自定义图表 的第 15 页
(4)颜色也可以分别自定义设置
(5)可以将形状显示为百分比模式,默认是占最大值的百分比,可以手动调整。
(4)数据标签
(6)可以将柱形图调整为条形图或折线图
(7)将类别放到行或列上,还能变成卡片图
分区 自定义图表 的第 16 页
分区 自定义图表 的第 17 页
应用于热词搜索,比如:有党支部的企业事业单位,开会解读十几大精神,关于热词也可以做词云
15.词云图【Word Cloud】
2020年8月16日 21:45
分区 自定义图表 的第 18 页
根据需要对格式进行调整
这个插件的参数设置太简单,可设置的参数如下,
再有就是显示条目的数量与动画播放的速度的调整:
topN:条形图中的条目数量
duration:动画延迟时间,值越大动画播放越慢
autoPlay:设置是否自动播放
fontFamily:设置条目的字体
fontSize:设置条目的字号
hideGrid:设置是否因此图例
hideNumber:设置是否因此数据值
hidePeriod:设置是否显示日期
periodSize:设置日期的字号
16.动画条形图 【Animated Bar Chart Race】
2020年8月17日 0:10
分区 自定义图表 的第 19 页
17.蝴蝶结图(Bowtie chart)
2020年8月18日 18:46
分区 自定义图表 的第 20 页
编辑里面可以设置
18.占比小人图(Walkers Animated Pictogram)
2020年8月18日 20:26
分区 自定义图表 的第 21 页
19.评分图表(Ratings by MAQ Software)
2020年8月18日 20:32
分区 自定义图表 的第 22 页
全球各国地图下载:https://www.amcharts.com/svgmaps/
(网址速度慢,有时打不开,换个时间段就可以了)
SVG地图省市区县
https://axhub.im/maps/
SVG世界各国/地区地图
https://www.amcharts.com/svg-maps/
在线制作像素SVG地图
https://pixelmap.amcharts.com/
JSON地图省市区县
http://datav.aliyun.com/tools/atlas/
地图格式转换
https://mapshaper.org/
JSON地图修改
https://geojson.io
20.SVG地图(Synoptic Panel)
2020年8月18日 20:43
分区 自定义图表 的第 23 页
附1:图表交互-工具提示
2020年8月18日 19:22
分区 自定义图表 的第 24 页
(1)第1页上做图
(2)第2页上是钻取页
附2:跨页钻取
2020年8月18日 19:22
分区 自定义图表 的第 25 页