两个文件:xlsx与pbi都在网盘
一、常见公式套路:
重要:理解dax公式有3点:
1.对 DAX 从内层向外逐层分解
2.内部上下文(例如sumx类迭代函数的所在的行),内部筛选条件(filter或布尔值),外部上下文(表格或矩阵的行标签),外部筛选条件(切片器等)
3.计算方式(聚合函数)怎么运行,在哪里运行。
1.DAX公式的灵魂:Calculate
1.1 筛选条件为空,不影响外部上下文
销量=calculate(sum(销量))
1.2 添加限制条件,缩小上下文
华为销量=calculate(sum(销量),' 产品明细 '[产品名]="华为")
1.3 结合 ALL 函数,扩大上下文
所有手机销量=calculate(sum(销量) ,all( ' 产品明细 '[产品名]))
1.4 结合 ALL 函数,重置上下文
所有手机销量=calculate(sum(销量) ,all( ' 产品明细 '[产品名]),' 产品明细 '[类别]="手机"))
2.单位成本
单位成本 =
CALCULATE (MIN (' 成本表 '[成本金额]),
TOPN (1,
FILTER (ALL (' 成本表 '), ' 成本表 '[生效日期]<= EARLIER (' 订单表 '[订单日期])&&
' 成本表 '[产品名称]=EARLIER (' 订单表 '[产品名称])),
' 成本表 '[生效日期] ) )
(EARLIER 函数只能用在添加计算列,EARLIER 函数只能用在添加计算列,EARLIER 函数只能用在添加计算列)
成本表.png成本单价.png
2.1EARLIER 函数返回当前行对应的参数列,就是返回本行和参数列交叉的单元格。
2.2.FILTER (ALL (' 成本表 '), ' 成本表 '[生效日期]<= DATE (2018,2,8)&&' 成本表 '[产品名称]="手机")
2.3TOPN 函数返回按生效日期排序的第 1 行。
2.4MIN 函数,实际上用 MAX 函数也是一样的,因为只有一个值,最小值和最大值是相同的,如果是标量值(数字)sum也可以。
2.5CALCULATE (MIN (' 表 2'[采购单价]),' 表 2')
链接:https://www.jianshu.com/p/f71ba89b9704
3.类别最大销售量字段名
类别最大销售量字段名 =
if(
HASONEVALUE('订单表'[子产品类别]),"",CALCULATE(MAX('订单表'[子产品类别]),
FILTER('订单表','订单表'[销售量]=MAX('订单表'[销售量]))))
3.1 新建表看看筛选的结果,表 = FILTER('订单表','订单表'[销售量]=MAX('订单表'[销售量]))
max表.png
3.2 CALCULATE+MAX ('订单表'[子产品类别])) 的方式获取当前筛选条件下的[子产品类别]) 名称(ps: 此处 max 只是为了将单一行聚合为值,也可以使用 min 替代)
3.3 if+HASONEVALUE('订单表'[子产品类别])返回值是否行的值,等于就返回空
类别最大.png
4.产品内排名
产品内排名 =
RANKX(ALLEXCEPT('订单表','订单表'[产品名称]),CALCULATE(SUM('订单表'[销售量])))
在网页看到感觉这个公式其实是有问题的建议用:
自己简书的见解链接:https://www.jianshu.com/p/f521b9c7768d
rankx-all排序 = RANKX(ALL('订单表'[子产品类别]),CALCULATE(SUM('订单表'[销售量])))
产品内排名.png
如图产品名称列有两个筛选:电脑手机类还有子产品类共同筛选出一个子集(电脑—戴尔),allexcept保留了产品名称的筛选但是清除了子产品类的筛选(就剩下电脑类【戴尔,惠普,联想】,然后传递给外层的rankx排序)
5.销量排名第二的子产品
套用上面两个公式可以得到排名第二的产品名称
销量排名第二的子产品 =
CALCULATE(MIN('订单表'[子产品类别]),
FILTER(VALUES('订单表'[子产品类别]),
RANKX(ALL('订单表'[子产品类别]),CALCULATE(SUM('订单表'[销售量])))=2))
排名第二.png
链接:http://dy.163.com/v2/article/detail/DSF5GJJF0516GFHA.html
6.中国式报表
//新建一个表输入公式
多层级表 =
var a =ADDCOLUMNS(SELECTCOLUMNS('订单表',"L2",'订单表'[子产品类别],"L1",'订单表'[产品名称]),"L0","类别")
var b =ADDCOLUMNS(SELECTCOLUMNS('订单表',"L2",BLANK(),"L1",'订单表'[月份]),"L0","月份")
return UNION(a,b)
行列交叉的计算
多层级销售量 =
SWITCH(TRUE(),
SELECTEDVALUE('多层级表'[L0])="月份",
CALCULATE(SUM('订单表'[销售量]),TREATAS(VALUES('多层级表'[L1]),'订单表'[月份])),
SELECTEDVALUE('多层级表'[L0])="类别",
CALCULATE(SUM('订单表'[销售量]),TREATAS(VALUES('多层级表'[L2]),'订单表'[子产品类别])))
中国式报表.png
链接:https://www.cnblogs.com/yeacer/p/9330081.html
7.总计不等于明细之和
大于2000的城市销量 =
CALCULATE('订单表'[总销售量],FILTER(ALL('订单表'[城市]),'订单表'[总销售量]>1500))
总计不等于明细之和.png
筛选条件:外部上下文的子类别(例如oppo)和内部筛选城市销量大于1500
魅族,小米,总计的数值都是独立计算的,都是正确的(总计算的是城市销量大于1500的所有总和),但是这图很别扭。
解决办法:
1.隐去总计
= IF (HASONEVALUE (' 订单表 '[子产品类别]),
[大于2000的城市销量]),
BLANK()
//HASONEVALUE 判断外部上下文(例如总计)是否在订单表 '[子产品类别]中
= SUMX (' 订单表 ', [大于2000的城市销量]])
//或者
= SUMX (FILTER (DISTINCT('订单表'[城市]),[总销售量]>1500),'订单表'[总销售量])
sumx是迭代函数,根据行标和'订单表'[城市]的交集查找并累计求和,而总计不在'订单表'[城市]中,就直接累计前面的求和
8.动态分析
8.1动态指标计算
输入数据,新建一个表给切片器
切片器水平设置
切片器水平设置.png
switch+selectedvalue创建动态度量
销售金额或销售数量指标 =
SWITCH(TRUE(),
SELECTEDVALUE('指标表'[分析指标])="销售金额",'订单表'[销售金额],
SELECTEDVALUE('指标表'[分析指标])="销售量",'订单表'[总销售量])
动态指标.gif
8.2动态坐标轴
动态指标轴表 =
VAR a =
SELECTCOLUMNS (
ADDCOLUMNS ( VALUES ( '订单表'[城市] ), "城市 — 子产品指标", "城市" ),
"城市 — 子产品指标", [城市—子产品指标],
"指标明细", [城市]
)
VAR b =
SELECTCOLUMNS (
ADDCOLUMNS ( VALUES ( '订单表'[子产品类别] ), "城市 — 子产品指标", "子产品" ),
"城市 — 子产品指标", [城市—子产品指标],
"指标明细", [子产品类别]
)
RETURN
UNION ( a, b )
SELECTCOLUMNS新建一个两列表,ADDCOLUMNS从别的表选一列并重命名。建好指标轴表,将[城市—子产品指标]放到切片器
动态订单数据 =
IF (
HASONEFILTER ( '动态指标轴表'[城市—子产品指标] ),
SWITCH (
TRUE (),
SELECTEDVALUE ( '动态指标轴表'[城市—子产品指标] ) = "城市", CALCULATE (
SUM ( '订单表'[销售量] ),
TREATAS ( VALUES ( '动态指标轴表'[指标明细] ), '订单表'[城市] )
),
SELECTEDVALUE ( '动态指标轴表'[城市—子产品指标] ) = "子产品", CALCULATE (
SUM ( '订单表'[销售量] ),
TREATAS ( VALUES ( '动态指标轴表'[指标明细] ), '订单表'[子产品类别] )
)
),
ERROR ( "数据错误,请单选坐标轴指标!" )
)
利用treatat函数关联动态指标轴表和订单表
动态坐标轴.gif9.返回文本或者日期
sumx可以返回日期,但不能返回文本
earlier返回下一天时间 = SUMX(FILTER('销售情况',[序号]=EARLIER([序号])+1),'销售情况'[日期])
增加计算列利用calculate+min+earlier返回文本或日期
earlier返回文本 =
CALCULATE(MIN('成本表'[产品名称]),FILTER('成本表','成本表'[产品名称]=EARLIER('成本表'[产品名称])))
10.时间智能函数
运用时间智能函数最好新建一个日期表,并与其它有关的表关联
时间智能函数.jpg时间智能函数分为以下两类:
10.1 返回期间的时间智能函数
10.2 返回期间并执行运算的时间智能函数
链接:https://www.jianshu.com/p/10ffe1c2fb0a
求同期问题,昨天,上个月,上一年
1.利用today函数做锚点
2.利用{}把计算出的值转化为表
11.其他合并数值太大排序在最前问题
字段其他合并后数值总是排第一
其他合并排序.png
12.累计问题
12.1 M语言累计
Table.SelectRows筛选出比当前行小于等于的行,深化出[销售量],然后list.sum求和
= Table.AddColumn(已添加索引, "M累计", each List.Sum (Table.SelectRows (已添加索引,(x)=>x[索引]<=_[索引])[销售量]))
12.2 earlier累计
最重要的是新建索引列,因为销售量有可能相等,做不了比较
earlier累计 = CALCULATE(SUM('累计'[销售量]),FILTER(ALL('累计'),'累计'[索引]<=EARLIER('累计'[索引])))
12.3 var累计
推荐用var的方式,因为比earlier灵活且不需要新建列
var累计 = var a = [索引]
return CALCULATE(SUM('累计'[销售量]),FILTER(ALL('累计'),'累计'[索引]<=a))
sumx替换calculate也可以
13.快速度量值-星级评分
星级评分.png14.占比问题
ALL 和 ALLSELECTED 计算得出的4个占比
链接:https://www.jianshu.com/p/85ff4468ff4f
链接:https://www.jianshu.com/p/94970d0a0a1e
15.求移动平均迭代问题(不明白,以后再想)
正确写法,分开两个度量值
销售额1 = SUM('移动平均'[销售额])
移动平均 = AVERAGEX(DATESINPERIOD('日期表'[日期],MIN('日期表'[日期]),-3,DAY),[销售额1])
正确
合并写法显示错误的数据
移动平均sum = AVERAGEX(DATESINPERIOD('日期表'[日期],MAX('日期表'[日期]),-3,DAY),SUM('移动平均'[销售额]))
错误,两个数值重叠
为什么直接在公式内求和就出错了?永远得到当日的数据,而不是3天内的平均求和?sum是sumx的特殊形式,再建一个sumx的销售额看看
sumx销售额 = SUMX('移动平均',SUM('移动平均'[销售额]))
移动平均sumx = AVERAGEX(DATESINPERIOD('日期表'[日期],MAX('日期表'[日期]),-3,DAY),[sumx销售额])
** 1.这个公式内的度量值sum销售额或sumx销售额不受行上下文的影响,averagex的第一个参数传递给第二个参数(成为sumx的过滤条件)然后计算,如果在内部直接写聚合函数,聚合函数收到行上下文的影响,总是返回当前日期的销售额(3天的销售额都是当天再除以3,所以这个平均是错误的)。**
2.第二个解答:sum包含一个隐式的calculate,averagex的第一个参数作为calculate的筛选条件
16.earlier与earliest函数引发的上下文思考
(重要)
EARLIER()和EARLIEST()函数是DAX中较难掌握的两个函数
链接:https://zhuanlan.zhihu.com/p/32554533
17.模型关系
USERELATIONSHIP 可以激活候选关系
候选关系:假设两张表,订单表和日期表,订单表中有订单日期和发货日期,需要按照订单日期和发货日期分别进行分析,可是两个表之间只能有一个字段与日期表建立关系,如果按照订单日期与日期表建立了实线关系,那么发货日期与日期表建立的关系只能用虚线表示(激活后该计算中会屏蔽其中的实线关系)。
TREATAS 函数建立虚拟关系
各种关系的的速度:
实线连接:速度最快
备用的虚线:速度次之
虚拟关系:速度最慢,但最灵活,不受原模型的影响。
18.关系的筛选
单方向筛选只能从一端传递到另一端,双方向是相互的
筛选交叉.png
19.词根模糊匹配
匹配的城市 =
FIRSTNONBLANK (
FILTER (
VALUES ( '城市-词根模糊匹配'[城市] ),
SEARCH ( '城市-词根模糊匹配'[城市], '详细地址-词根模糊匹配'[地址], 1, 0 )
),
1
)
SEARCH ( '城市-词根模糊匹配'[城市], '详细地址-词根模糊匹配'[地址], 1, 0 )返回数字,
FILTER (VALUES ( '城市-词根模糊匹配'[城市] ),数字),数字代表True,所以返回values一列
然后就不知道FIRSTNONBLANK 的原理了。
(留以后研究)
20.lookupvalue
查找语文成绩是90,数学是100的学生姓名
lookupvalue结果 =
LOOKUPVALUE ( '雷达学生成绩 透视'[学生姓名], '雷达学生成绩 透视'[语文], 90, '雷达学生成绩 透视'[数学], 100 )
21.条件筛选:HASONEVALUE vs. ISFILTERED vs. HASONEFILTER
=IF(HASONEVALUE(Stores[StoreName]), [Units Sold], BLANK())
=IF(ISFILTERED(Stores[StoreName]),[Units Sold],BLANK())
=IF(HASONEFILTER(Stores[StoreName]),[Units Sold],BLANK())
链接:https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/
作图技巧
1.其他合并数值太大排序在最前问题
在powerquery增加列
自定义序号.png
工具提示排序.png
这里偷懒了,其他为2,剩下的就是1,应该在数据源对字段的销量进行从大到小标记序号
2.新建表
直接新建表
2.2 合并 = union ('1 月 ','2 月 ')
2.1 先把这个表中的 [产品] 列和合并表中的 [商品名称] 建立关系,
join联结 = NATURALINNERJOIN (' 合并 ',' 采购表 ')
2.3 提取维度表
产品表 = DISTINCT (' 合并 '[名称])
2.帕累托图问题
帕累托图.png 帕累托1.png上面的图有两个错误的帕累托图是因为其数据源非重复,造成累计占比计算出错
3.参数表与动态卡片(文本拼接计算结果)
在 PowerBI Desktop 中,在 “建模” 选型卡下,点击 “新建参数”,
参数值 = SELECTEDVALUE (' 参数 '[参数])
前N名利润 =
"前"&[参数值]&"名利润总计"&UNICHAR(10)&CALCULATE([sum利润],TOPN([参数值],ALL('帕累托表'),[sum利润]))&"元"
UNICHAR(10)是换行符
卡片的显示方式可以去下载自定义可视化对象 - Rotating Tile
动态卡片教程链接:https://www.jianshu.com/p/a1a129fdb38d
4.书签
4.1普通书签跳转
从上方视图打开书签窗格,为谋一页报表制作书签,添加按钮(图片,形状皆可),操作选择书签
在桌面版PowerBI,按住Ctrl+鼠标点击才能跳转,网页发布版只需要点击
4.2也可以幻灯片方式放映:
4.3配合按钮同一页面内部分视觉图像的转换
如下图
重要:打开书签窗格,选择窗格,选择隐藏或显示视觉图像
4.4.切片器不同书签页面传递筛选结果
有两种方法:
1.利用同步切片器
同步切片器.png
2.制作书签时部锁定数据
切片器不同标签页面传递数据.png5.自制作的切片器组隐藏
制作两个书签(隐藏或显示切片器组),添加两个按钮用于控制书签
参考链接:https://www.cnblogs.com/yeacer/p/9844624.html
5.筛选交互
选中一个视觉,点击菜单的格式窗格,然后点击编辑交互
筛选传动可以点击视觉图像上的【突出显示】或者【禁止筛选】按钮
筛选交互.gif
6.排序技巧
类似文本的【1,2,3,12】,排序变成【1,12,2,3】
或者【一月,二月,三月】,排序变成【二月,三月,一月】
解决办法:在数据编辑页为需要排序的文本添加序号,然后排序按照序号来排
DaxStudio
6.1编辑
EVALUATE
ROW("总分数",SUM('雷达学生成绩'[分数]))
以EVALUATE开头,并且可以在多个公式前加EVALUATE,主要用来逐步调试查看多个嵌套公式的结果
6.2格式化 DAX 代码
点击Format Query即可,在选项里可以选长/短格式
6.3从 pbix中导出数据
//导出一个现有表
EVALUATE
' 学生成绩表'
//条件过滤
EVALUATE
FILTER(' 学生成绩表 ',YEAR(' 学生成绩表 '[姓名])="小明")
//导出汇总表并按照年度和月份进行排序
EVALUATE
ADDCOLUMNS (
SUMMARIZE (
' 日期表',
' 日期表 '[年度],
' 日期表 '[月份]
),
"销售额", [销售金额]
)
ORDER BY
' 日期表 '[年度],
' 日期表 '[月份]
6.4导出所有表
在默认的功能区中没有显示导出所有表这个功能,在 option 中切换到 Advanced 页面,勾选:Show Export All Data button
6.5导出所有字段信息
在不需要导出所有数据的情况下,可以方便地查看字段分别是什么类型、大小分别是多少,以便对模型有个整体的了解
SELECT dimension_name AS table_name,
attribute_name AS column_name,
datatype,(dictionary_size/1024) AS size_kb
FROM $system.discover_storage_table_columns
WHERE dictionary_size > 0
6.6导出 PowerBI 文件中所有度量值
select
MEASURE_NAME,
EXPRESSION
from $SYSTEM.MDSCHEMA_MEASURES
where MEASURE_AGGREGATOR = 0
order by MEASUREGROUP_NAME
6.7测试性能
链接:
https://www.jianshu.com/p/40b8c685583e
https://www.jianshu.com/p/8d1220aabd04
https://www.jianshu.com/p/185edd99083f
https://mp.weixin.qq.com/s/nWqrV3OXZKp1T2rCRv8bJw
7.分组
7.1新建表
7.2度量值
分组表销售金额 = SUM('分组'[销售额])
//一定要先写这个总销售额,下面的averagex是迭代函数才能正常运行
按平均分组销售 =
VAR avg_sale =
AVERAGEX (
ALL ( '分组'[品类] ),
[分组表销售金额]
)
RETURN
IF (
[分组表销售金额] >= avg_sale
&& SELECTEDVALUE ( '分组平均表'[按平均分组] ) = "高于平均值",
[分组表销售金额],
IF (
[分组表销售金额] < avg_sale
&& SELECTEDVALUE ( '分组平均表'[按平均分组] ) = "低于平均值",
[分组表销售金额]
)
)
7.3图例区分颜色,工具提示是用来按总金额排序的
7.4最后,年月用powerquery截取日期,加个年月切片器
网友评论