1、度量值与关系模型
1、查看关系模型
- 导入数据
点击左侧【数据】，可以查看表中数据。 - 关系模型
可以看到，商品表和销售表是一对多的关系；门店和销售表也是一对多的关系。
2、创建度量值
- 度量值概念
度量值就是对表中数据求和、最大、平均等。如果想获取所有销售数量的最大、最小，在Excel中也非常方便来做，但是如果想获取某天销售额的最大、最小，就不是很方便了，可以使用PowerBI。 - 创建度量值
度量值一般都放到一张表中。
点击【输入数据】,将表名称修改为度量值，则在字段菜单可以看到有一个表为“度量值”，里面的列是空的。
选中【度量值】表，点击【新建度量值】，计算
总销售数量 = sum('销售表'[销售数量])
最大销售数量 = MAX('销售表'[销售数量])
最小销售数量 = min('销售表'[销售数量])
平均销售数量 = AVERAGE('销售表'[销售数量])
总订单数 = COUNTROWS('销售表')
- 在【可视化】菜单中选择【矩阵】，拖动【门店名称】到行，拖动【总销销售数量】、【平均销售数量】、【总订单数】等到值。
- 修改图标的样式，如添加标题、颜色、字体。拖动销售表【日期】到行，可以删除日期中不想要的如（季度、月）等信息，也可钻取显示。
2、向下钻取与数据导出
- 获取数据
- 在视图窗口中选中【饼图】，将【品种】、【品牌】、【型号】依次拖到到图例，【销量】拖动到值
- 修改【格式】–【详细信息】–【大小】和标签位置
- 钻取功能
筛选
- 选择【筛选器】进行筛选。
导出
选择【文件】-【导出】
选择指定路径导出，这样其他工作人员就可以直接打开你导出的pbit。
如果想在打开的pbit文件中导出数据，直接点击钻取一行最后的[…]，选择【导出数据】。
如果想获取全部数据，选择左侧数据，右键选择【复制表】就可以复制全部数据。如果想获取某列数据选中列侯选择【复制】即可。
3、新建列与关系函数
示例-获取每件商品的销售金额
每件商品的销售金额=商品的单价*商品的数量
1、LOOKUPVALUE
语法：
LOOKUPVALUE(要返回的值,哪张表中搜索哪列,自己表根据哪列查找,[查找的列],[查找的值]…[当返回值有0个或多个时，默认返回的值])返回满足一个或多个列匹配条件所对应的值
参数：
第一参数：包含要返回的值的现有列的名称。 它不能是表达式。
第二参数：现有列的名称（要查找的范围），可以是第一参数表中的列，也可以是相关表中的列，但不能是表达式
第三参数：要在第二参数中查找的值
第四、五…参数：（可选）同第二、三参数，必须成对出现
最后一个参数：（可选）当满足查找条件返回的值为0个或多个时，默认返回的值。
2、使用同表中的数据新建列
- 在销售表中【新建列】为【售价】
售价 = LOOKUPVALUE('商品表'[售价],'商品表'[商品编码],'销售表'[商品编码])
在销售表中【新建列】为【销售金额】
销售金额 = '销售表'[销售数量]*'销售表'[售价]
2、使用关系函数新建列
上面的方式可以实现，但是必须先创建【售价】这列，如果不想创建【售价】我们该怎么办呢？可以使用函数：
多端找一端
RELATED在销售表中【新建列】为【销售金额2】
销售金额2 = [总销售数量]*RELATED('商品表'[售价])
示例-每件商品有多少订单
一端找多端
RELATEDTABLE在商品表中【新建列】
订单数 = COUNTROWS(RELATEDTABLE('销售表'))
4、Calculate引擎
1、Calculate引擎
1、筛选单个
CALCULATE(计算器,筛选器)
计算器=度量值示例-电视销量
新建度量值【电视销量】
电视销量 = CALCULATE([总销售数量],'商品表'[商品编码]="A03")
2、筛选多个
筛选多个使用in 或 not in
不包含电视的销量
不包含电视的销量 = CALCULATE([总销售数量],'商品表'[商品名称] in {"A01","A02","A04"})
冰箱的销售数量使用not in实现
冰箱的销量 = CALCULATE([总销售数量],not '商品表'[商品编码] in {"A01","A03","A04"})3、筛选多个条件
示例：商品编码等于“A03“和进价=2200
多条件 = CALCULATE([总销售量],'销售表'[商品编码]="A03" ,'商品表'[进价]=2200)5、Calculatedtable筛选表
1、Calculatedtable筛选表
示例：电视的所有销售订单创建一张新表
选择【表工具】中【新建表】
电视机销售表 = CALCULATETABLE('销售表','销售表'[商品编码]="A01" )
2、切片器的使用
如果想根据商品名称查看商品销量，可以使用【切片器】
- 选择【矩阵】，拖动【门店】到行，【总销售量】到值
- 选择【切片器】，拖动【商品名称】到行
- 修改显示样式，
6、高级筛选器Filter与Values人工造表
返回一个表，用于表示另外一个表或表达式的子集，不能单独使用
Filter函数对筛选的表进行横向的逐行扫描，这样的函数叫迭代函数
1、基本使用
高级筛选器Filter。
Filter(‘表’, 筛选条件）返回：表
Countrows(Filter(表，筛选条件））示例-筛选“电视”的订单数量
新建度量值：电视的订单数量
电视机的订单数量 = COUNTROWS(FILTER('销售表','销售表'[商品编码]="A03" ))
2、什么时候使用Filter函数呢？
示例：电视机的销量
电视机销量1 = CALCULATE([总销售量],'商品表'[商品编码]="A03" )
电视机销量2 = CALCULATE([总销售量],FILTER('商品表','商品表'[商品编码]="A03" ))
可以看到实现的效果是一样的，那什么时候使用Filter、什么时候不使用Filter呢？
在Calculate函数中的直接筛选条件里，我们只能输入：
‘表’[列] = 固定值 或 ‘表’[列] <> 固定值
‘表’[列] >= 固定值 或 ‘表’[列] <= 固定值
‘表’[列] > 固定值 或 ‘表’[列] < 固定值但是遇到如下情况，就要使用Filter函数
[列]=[度量值]、 [列]=公式、[列]=[列]
[度量值]=[度量值]、 [度量值]=公式、[度量值]=固定值示例-各门店电视销量大于40的
总销售数量 = sum('销售表'[销售数量])
电视销量 = CALCULATE([总销售数量],'商品表'[商品编码]="A03")
filter电视销量 = CALCULATE([电视销量],FILTER('门店',[电视销量]>40))
3、Values人工造表
Filter函数中使用的表必须是一端的表，我们使用【门店】可以显示，使用【销售表】可以看到没有办法显示，这时候可以使用Values函数人工造表
filter电视机销量2 = CALCULATE([电视机销量1],FILTER('销售表',[电视机销量1]>40))
filter电视机销量3 = CALCULATE([电视机销量1],FILTER(VALUES('销售表'[商品编码]),[电视机销量1]>40))
7、上下文
1、新建列是行上下文
可以看到在每行上都显示746
2、度量值是筛选上下文
创建【度量值】总销售数量
可以筛选各门店的销售数量。
总结：
- 度量值天生具有筛选功能
- 新建列是行上下文，行上下文没有筛选功能
- 想让行上下文实现筛选功能就要在外套一个calculate函数
8、x系列函数
x系列函数有：SUMX、AVERAGEX、MINX、MAXX用法相同。
语法：
Sumx('表名',算术表达式)- 逐行扫描
- 执行运算，每一行都会返回一个值
- Sumx函数记住了每一行返回的值，最后把所有的值相加求和
示例：在【销售表】新建列【毛利1】
毛利1 = SUMX('表',[销售金额]-[销售成本])
可以看到毛利1这列返回的值不是该销售记录的毛利，而是对所有毛利进行求和。如果想获取所对应的毛利，则需要添加calculate函数
毛利2 = CALCULATE(SUMX('表',[销售金额]-[销售成本]))
9、Earlier函数
Earlier函数：获取当前行
Earlier(表[列])示例：某商品累计销量
filter('销售表','销售表'[货号] =earlier('销售表'[货号]))
'销售表'[销售日期]<= earlier('销售表'[销售日期])
销售累计 = sumx(FILTER(FILTER('销售表','销售表'[货号] =earlier('销售表'[货号])),'销售表'[销售日期]<= earlier('销售表'[销售日期])),'销售表'[销量])
10、安全除法与逻辑判断
1、安全除法
可以看到分母为0 时候，没有结果。安全出发指如果分母为0时候，结果为0
安全除法 = DIVIDE([分子],[分母],0)
2、逻辑判断
如果显示数据表头没有
选中【字段】中的表，右键【编辑查询】，选择【将第一行用作标题】，点击【关闭并应用】
1.IF(logical_test>,<value_if_true>, value_if_false)
--检查是否满足作为第一个参数提供的条件。 如果该条件为 TRUE，则返回一个值；
如果该条件为 FALSE，则返回另一个值，用法与excel类似称呼 = IF('称呼'[性别]="男","先生","女士")
2.SWITCH(<expression>,条件1,值1,条件2,值2) --根据值列表计算表达式，
并返回多个可能的结果表达式之一。示例：switch表中新建列 【月份】
月份 = SWITCH('switch'[Column1],
1,"一月",
2,"二月",
3,"三月",
4,"四月",
5,"五月",
6,"六月",
7,"七月",
8,"八月",
9,"九月",
10,"十月",
11,"十一月",
12,"十二月",
"未能识别")注意：如果表达式比较长，可以按住【Alt】+【Enter】进行换行
ISBLANK(column) --判断该列中某个值是否为空
ISNONTEXT(column) --检查某个值是否不是文本
ISNUMBER(column) --检查某个值是否为数字
ISTEXT(column) --检查某个值是否为文本
ISLOGICAL(column) --检查某个值是否是逻辑值11、文本函数
1、find
find(查找哪个字符,去哪里查找,从哪一个位置查找,如果找不到返回什么值)
第三个和第四个参数可以省略示例：根据商品名称得出是上装还是下装
可以看到上装的商品种类多，而下装中包含“裙”或者“裤”的
裤 = find("裤",'商品表'[商品名称],1,100 )
裙 = find("裙",'商品表'[商品名称],1,100)
下装或上装 = if (FIND("裤",'商品表'[商品名称],1,100) <> 100 || find("裙",'商品表'[商品名称],1,100) <> 100,"下装","上装")2、search
search(查找哪个字符,去哪里查找,从哪一个位置查找,如果找不到返回什么值)
第三个和第四个参数可以省略注意：find查找字符区分大小写，search不区分大小写，其它功能完全一样
示例：在商品表中根据商品名称获取是“上装”还是“下装”
3、left、right、mid
left从左向右取=left([字段名],取几个字符)
right从右向左取=right([字段名],取几个字符)
mid从中间开始取=mid([字段名],从第几个取,取几个)
字符长度= len([字段名])示例：
前5位 = LEFT('身份证号'[身份证号],5 )
后5位 = RIGHT('身份证号'[身份证号],5 )
中间五位 = MID('身份证号'[身份证号],3,5 )4、其他
REPLACE(),SUBSTITUTE() --字符串替换函数
TRIM(),LOWER(),UPPER()12、中文排序
- 导入数据
- 选择【矩阵】拖动【季节】到行，【销量】到列
- 这时发现季节列的顺序是春 、冬、秋、夏，是按汉语拼音进行排序
- 打开数据，可以看到数据中的顺序是我们想要的，选中【季节】表，右键【编辑查询】则可以添加列，【关闭并应用】
- 数据中，选中【季节】，点击【按列排序】选择索引
可以发现，这时候的矩阵按春夏秋冬顺序排序了 - 但是有时候，表中的顺序并不是排好序的
- 点击【输入数据】新建表【月份排序表】，表中有【月份】和【序号】两列
- 点击【模型】可以看到，【销量】表和【月份排序表】自动进行连接
- 选择【月份排序表】，选择【选择排序】，按序号排序
- 选择【矩阵】，拖动【月份排序表】中【月份】到行，拖动【销量】到值
这时就按照月份进行了排序。
13、表连接
交叉相同
intersect(表1,表2)
示例：
到会并邀请表 = INTERSECT('到会','邀请' )除去相同
Except(表1,表2)
示例：
发出邀请没来表 = EXCEPT('邀请','到会' )
到会没有邀请表 = EXCEPT('到会','邀请')全部：Union(表1,表2)示例：
全部数据表 = UNION('到会','邀请' )笛卡尔积
笛卡尔积 = CROSSJOIN('表1','表2')14、VAR定义变量
示例：表中成绩进行评价，如果总分大于270则为"优秀"，其余“一般”
普通方式实现：新创建列【总成绩】，使用if条件进行判断
如果数据量比较大，我们使用var实现
评价2 =
var zongfen=[数学]+[英语]+[语文]
return if(zongfen>=270,"优秀","一般")注意：变量名不能使用中文
示例2：标记【姓名】表中重复姓名是第几次出现
普通方式实现：
次数 = COUNTROWS(FILTER('姓名','姓名'[姓名]=EARLIER('姓名'[姓名]) && '姓名'[序号]<=EARLIER('姓名'[序号])))
var代替当前行函数来实现
次数2 =
var mname = '姓名'[姓名]
var mno = '姓名'[序号]
return COUNTROWS(FILTER('姓名','姓名'[姓名]=mname && '姓名'[序号]<=mno))15、ALL函数实现占比显示
1、ALL函数
ALL函数是清除筛选条件
ALL函数的应用：清除表中所有行的筛选器以及创建针对表中所有行的计算非常有用
ALL语法：
ALL(表名) 或 ALL(列，列…)返回表
示例：计算销量的占比
- 表中的数据
- 创建度量值【销售总量】
销售总量 = sum('表'[销量] )- 创建度量值【ALL销售量1】
ALL销售量1 = CALCULATE([销售总量],ALL('表'))- 创建【矩阵】，拖动【销售总量】到值，【商品种类】到行，【包装规格】到列
再选择【矩阵】，拖动【All销售量1】到值，【商品种类】到行，【包装规格】到列
可以看到【ALL销售量1】筛选的就是一个总销售量的总值
- 使用筛选功能筛选的数量除以【ALL销售量1】，则就是该销量所占比
占比1 = [销售总量]/[ALL销售量1]
默认的小数位数是2位，可以进行修改
- 添加【切片器】，拖到【商品】名称到字段。选择某几种商品。
可以看到总计不是100%，剩余所占百分比是没有选中的商品。如果想让选中的商品总计为100%。可以使用Allselected函数。
2、Allselected函数
语法： Allselected（表或列）用途：统计直观合计，即清除所显示的筛选条件
- 创建度量值【ALLselected销售量】
Allselected销售量 = CALCULATE([销售总量],ALLSELECTED('表' ))
- 创建度量值【占比2】
占比2 = [销售总量]/[Allselected销售量]
3、ALL(列)
ALL销量2 = CALCULATE([销售总量],ALL('表'[包装规格] ))
每个商品种类，不同包装规格所占比
3、Allexcept函数
Except中文翻译除……之外
所以该公式的含义是：除指定的某列之外，清除筛选条件
CALCULATE([销售总量],ALLEXCEPT('商品表','商品表'[商品种类])) 除商品名称这列之外，清除筛选条件16、Hasonevalue和Selectedvalue
1、Hasonevalue 函数
Hasonevalue, 顾名思义，只有一个值，它返回的是真或假，即判断是否只有一个值。
If (Hasonevalue('表'[列]), [度量值], blank())示例：各个门店销售数量大于200的销量
- 创建度量值【总销售数量】
- 创建度量值【各门店销售数量大于200】
各门店销售量大于200 = CALCULATE([销售总量],FILTER('门店',[销售总量]>200))
- 可以看到各门店销售量大于200这列的【总计】也显示，如果不想让他显示，可以使用Hasonevalue 函数。
hasonevalue销量 = if(HASONEVALUE('销售表'[日期].[日]),[各门店销售量大于200],BLANK())
说明：当求总计时，筛选上下文，不是唯一值，所以返回空值
2、Selectedvalue
当指定列中只有一个值时返回该值，否则返回替代结果，省略返回空值
selectedvalue 与if+hasonevalue等价，而且公式更简洁
if(Hasonevalue('表名'[列名]),返回值,black())#如果指定列唯一值，那就返回值，否则返回空
Selectedvalue('表名'[列名],[代替(省略返回空)])#当指定列中只有一个值时返回该值，否则返回替代结果，省略则返回空值示例
selected销量 = IF(SELECTEDVALUE('销售表'[日期].[日]),[每日销售数量大于200])
selected销量2 = SELECTEDVALUE('销售表'[日期])
17、排序
1、Rankx函数
在Power BI中如果想对数据排序，可以使用DAX中的RANKX函数，格式如下：
RankX(表名，表达式或度量值)- 加载数据
- 分别按各“品种”的销售金额排序
RankX函数第一个参数是表名，要按不同品种排序，分别对不同品种创建表
FILTER('品种销售表','品种销售表'[品种]=EARLIER('品种销售表'[品种]))
单品销售排名 = RANKX(FILTER('品种销售表','品种销售表'[品种]=EARLIER('品种销售表'[品种])),'品种销售表'[销售金额])
所有商品，同一种类都按“销售金额”进行排序。
默认的排序方式是降序，如果想修改为升序，留一空位，加asc
单品销售排名 = RANKX(FILTER('品种销售表','品种销售表'[品种]=EARLIER('品种销售表'[品种])),'品种销售表'[销售金额],,ASC)2、TopN排序
TopN则批量返回结果，从一张表中返回所有满足条件的前N行记录。
TopN语法：
TopN(N值,表名,[表达式],[顺序可选项])N值：表示排名前N位
表名：想要提取的表
表达式：按什么度量值来排序
顺序可选项:0降序，1升序
应用1：销售前3名门店的销售额
销量前3的销量 = CALCULATE([总金额],TOPN(3,VALUES('topn排序'[店号]),[总金额],DESC))应用2：前3名的销售量占比
占比 = [销量前3的销量]/[总金额]
18、日期时间函数
1、常用的日期函数
1、组合日期
DATE(2009,7,8) --结果返回'2009/7/8 0：00：00'
DATEVALUE("8/1/2009") --将文本形式的日期转换为日期时间格式的日期，结果返回
'2009/8/1 0：00：00'
YEAR(),MONTH(),DAY(),HOUR(),MINUTE(),SECOND()
NOW() --返回当前的日期时间 TODAY()--返回当前的日期示例：DATE函数
合并日期 = DATE('DATE'[年],'DATE'[月],'DATE'[日] )
2、获取年、月、日等
示例：提取“年”、“月”、“季度”、“分钟”
提取年 = YEAR('DATE'[合并日期])
提取月 = MONTH('DATE'[合并日期])
季度 = QUARTER([合并日期])
分钟 = MINUTE([现在时间])3、获取当前时间
当前日期 = NOW()
当前日期2 = TODAY()
如果只需要年月日则可以使用today()函数，如果是年月日时分秒需要使用now()。
4、获取星期
语法格式：
WEEKDAY([日期],1) --一周中的第几天,参数1代表从星期日开始计数，参数2代表从星期一开始计数
参数3代表从星期一开始计数，但到星期六结束
WEEKNUM([日期],1) --一年中的第几周，1代表从周日开始计数，2代表从周一开始计数示例：
星期 = WEEKDAY([当前日期],2)
一年中第几周 = WEEKNUM([合并日期],2 )5、日期计算
语法：
DATEADD([日期],3,DAY) --日期加减函数
EOMONTH([日期],3) --返回指定月份数之前或之后的月份的最后一天的日期
EDATE([日期],3) --返回在开始日期之前或之后指示的月数的日期
计算两个日期之间间隔--DATEDIFF
[时间间隔]:=
DATEDIFF([开始日期],
[结束日期],
'Y') --参数"Y"换成"M"或"D",则分别对应月和日
--也可以改成HOUR,MINUTE,SECOND计算小时，分，秒示例：
一个月后 = EDATE([合并日期],1 )
一个月前 = EDATE([合并日期],-1 )
一个月以后那个月的最后一天，例如查询一个月之后，最后一天的库存
一个月最后一天 = EOMONTH([合并日期],1)
计算保质期 [到期时间]-[起始时间]
保质期 = DATEDIFF([起始时间],[到期时间],DAY)+ 16、时间组合
示例：
#组合时 分 秒 time
组合时分秒 = TIME([小时],[分钟],[秒] )
2、生成日期表
如果数据模型中没有日期表，不用去找一个日期表导入进来，可以直接输入以下代码生成日期表。
日期表2 = 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])
)选择【新建表】，复制代码就可以创建日期表
3、日期智能函数
1、日期累加
1.计算年初至今累计--TOTALYTD函数
2.计算月初至今累计--TOTALMTD函数
3.计算季初至今累计--TOTALQTD函数
例如计算年初至今累计的销售金额：
[年累计金额]：=
TOTALYTD([销售金额],
'日历年'[日期])
# 如果要加上一个时间截止点，则写为：
[年累计截止金额]：=
TOTALYTD([销售金额],
'日历年'[日期],
'日历年'[日期]<DATE(2016,6,1))
# 同理计算季度初至今累计--TOTALQTD, 计算月初至今累计--TOTALMTD示例：年初至今销量
总销售金额 = SUM('销售表1'[销售] )
年初至今销售 = TOTALYTD([总销售金额],'销售表1'[日期])注意：如果计算的不是从1月1日开始，例如从7月1日开始计算为一年
年初至今7月1日开始 = TOTALYTD([总销售金额],'销售表1'[日期],"6-30")
年初至今4月1日之前 = TOTALYTD([总销售金额],'销售表1'[日期],'销售表1'[日期]<DATE(2021,4,1))
年初至今4月1日开始 = TOTALYTD([总销售金额],'销售表1'[日期],'销售表1'[日期]>=DATE(2021,4,1 ))
月初至今 = TOTALMTD([总销售金额],'销售表1'[日期])
季度至今 = TOTALQTD([总销售金额],'销售表1'[日期])选择视图窗口【卡片图】显示
2、计算去年同期
同期年初至今 = TOTALYTD([总销售金额],SAMEPERIODLASTYEAR('销售表1'[日期]) )
同期月初至今 = TOTALMTD([总销售金额],SAMEPERIODLASTYEAR('销售表1'[日期]) )
同期季初至今 = TOTALQTD([总销售金额],SAMEPERIODLASTYEAR('销售表1'[日期]) )
3、指定时间内销量
指定日期销量 = CALCULATE([总销售金额],DATESBETWEEN('销售表1'[日期],DATE(2021,3,2),DATE(2021,4,1)) )其中：DATESBETWEEN(‘销售表1’[日期]）中的表中必须是唯一的。
4、一定间隔的时间段
时间段比如每个月的前10天，后10天销量，如果按上面的方式计算太麻烦了，可以使用时间段
1、获取每月的第一天和最后一天
#返回当月最后一天
最后一天 = ENDOFMONTH('销售表2'[日期])
#返回当月第一天
第一天 = STARTOFMONTH('销售表2'[日期])
2、datesinperiod函数
datesinperiod函数：指定开始日期,向前或向后多少天、月、季度、年
datesinperiod(‘日期表’[日期],”2018-10-1”, 30，day) 返回2018.10.1-2019.10.30示例：每月前5天销量
总销售额 = sum('销售表2'[销量])
每月前5天销量 = CALCULATE([总销售额],DATESINPERIOD('销售表2'[日期],STARTOFMONTH('销售表2'[日期]),5,DAY) )
示例：每月后3天销量
每月最后三天 = CALCULATE([总销售额],DATESINPERIOD('销售表2'[日期],ENDOFMONTH('销售表2'[日期]),-3,DAY) )
5、计算每年及季度销量
每年一月一日销量 = CALCULATE([总销售额],STARTOFYEAR('销售表2'[日期]) )
每年12月31日销量 = CALCULATE([总销售额],ENDOFYEAR('销售表2'[日期]) )
每季度的使用ENDOFQUARTER函数
