12、中文排序
1.导入数据
2.选择【矩阵】拖动【季节】到行,【销量】到列
3.这时发现季节列的顺序是春 、冬、秋、夏,是按汉语拼音进行排序
4.打开数据,可以看到数据中的顺序是我们想要的,选中【季节】表,右键【编辑查询】则可以添加列,【关闭并应用】
5.数据中,选中【季节】,点击【按列排序】选择索引
可以发现,这时候的矩阵按春夏秋冬顺序排序了
6.但是有时候,表中的顺序并不是排好序的
7.点击【输入数据】新建表【月份排序表】,表中有【月份】和【序号】两列
8.点击【模型】可以看到,【销量】表和【月份排序表】自动进行连接
9.选择【月份排序表】,选择【选择排序】,按序号排序
10.选择【矩阵】,拖动【月份排序表】中【月份】到行,拖动【销量】到值
这时就按照月份进行了排序。
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 =
次数2 = var mname = '姓名'[姓名] var mno = '姓名'[序号] return COUNTROWS(FILTER('姓名','姓名'[姓名]=mname && '姓名'[序号]<=mno))
15、ALL函数实现占比显示
1、ALL函数
ALL函数是清除筛选条件
ALL函数的应用:清除表中所有行的筛选器以及创建针对表中所有行的计算非常有用
ALL语法:
ALL(表名) 或 ALL(列,列…)
返回表
示例:计算销量的占比
1.表中的数据
2.创建度量值【销售总量】
销售总量 = sum('表'[销量] )
3.创建度量值【ALL销售量1】
ALL销售量1 = CALCULATE([销售总量],ALL('表'))
4.创建【矩阵】,拖动【销售总量】到值,【商品种类】到行,【包装规格】到列
再选择【矩阵】,拖动【All销售量1】到值,【商品种类】到行,【包装规格】到列
可以看到【ALL销售量1】筛选的就是一个总销售量的总值
5.使用筛选功能筛选的数量除以【ALL销售量1】,则就是该销量所占比
占比1 = [销售总量]/[ALL销售量1]
默认的小数位数是2位,可以进行修改
6.添加【切片器】,拖到【商品】名称到字段。选择某几种商品。
可以看到总计不是100%,剩余所占百分比是没有选中的商品。如果想让选中的商品总计为100%。可以使用Allselected函数。
2、Allselected函数
语法: Allselected(表或列)
用途:统计直观合计,即清除所显示的筛选条件
1.创建度量值【ALLselected销售量】
Allselected销售量 = CALCULATE([销售总量],ALLSELECTED('表' ))
2.创建度量值【占比2】
占比2 = [销售总量]/[Allselected销售量]
3、ALL(列)
ALL销量2 = CALCULATE([销售总量],ALL('表'[包装规格] ))
每个商品种类,不同包装规格所占比
3、Allexcept函数
Except中文翻译除……之外 所以该公式的含义是:除指定的某列之外,清除筛选条件 CALCULATE([销售总量],ALLEXCEPT('商品表','商品表'[商品种类])) 除商品名称这列之外,清除筛选条件
16、Hasonevalue和Selectedvalue
1、Hasonevalue 函数
Hasonevalue, 顾名思义,只有一个值,它返回的是真或假,即判断是否只有一个值。
If (Hasonevalue('表'[列]), [度量值], blank())
示例:各个门店销售数量大于200的销量
1.创建度量值【总销售数量】
2.创建度量值【各门店销售数量大于200】
各门店销售量大于200 = CALCULATE([销售总量],FILTER('门店',[销售总量]>200))
3.可以看到各门店销售量大于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(表名,表达式或度量值)
1.加载数据
2.分别按各“品种”的销售金额排序
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)+ 1
6、时间组合
示例:
#组合时 分 秒 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函数