powerbi知识大集合

作者: BI罗 | 来源:发表于2019-02-26 16:38 被阅读375次

    两个文件: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
    切片器水平设置
    切片器水平设置.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函数关联动态指标轴表和订单表

    动态坐标轴.gif

    9.返回文本或者日期

    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.快速度量值-星级评分

    星级评分.png

    14.占比问题

    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

    上面的图有两个错误的帕累托图是因为其数据源非重复,造成累计占比计算出错

    帕累托2.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也可以幻灯片方式放映:

    书签放映.gif

    4.3配合按钮同一页面内部分视觉图像的转换
    如下图
    重要:打开书签窗格,选择窗格,选择隐藏或显示视觉图像



    4.4.切片器不同书签页面传递筛选结果
    有两种方法:
    1.利用同步切片器
    同步切片器.png

    2.制作书签时部锁定数据

    切片器不同标签页面传递数据.png

    5.自制作的切片器组隐藏

    制作两个书签(隐藏或显示切片器组),添加两个按钮用于控制书签

    切片器隐藏与否.gif
    参考链接:https://www.cnblogs.com/yeacer/p/9844624.html

    5.筛选交互

    选中一个视觉,点击菜单的格式窗格,然后点击编辑交互
    筛选传动可以点击视觉图像上的【突出显示】或者【禁止筛选】按钮


    筛选交互.gif

    6.排序技巧

    类似文本的【1,2,3,12】,排序变成【1,12,2,3】
    或者【一月,二月,三月】,排序变成【二月,三月,一月】
    解决办法:在数据编辑页为需要排序的文本添加序号,然后排序按照序号来排

    排序.jpg

    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截取日期,加个年月切片器

    相关文章

      网友评论

        本文标题:powerbi知识大集合

        本文链接:https://www.haomeiwen.com/subject/vfqayqtx.html