|
透视分析,DAX公式(数据分析表达式),与Excel 函数与公式存在区别 一、导入数据1.将Power Pivot添加到功能栏1）文件>选项>自定义功能区>开发工具
2）开发工具>COM加载项>勾选Microsoft Power Pivot for Excel>确定
2.导入数据1）将数据加载到Power Pivot：用Power Query处理完数据>关闭并上载至>勾选将此数据添加到数据模型>确定 2）打开Power Pivot：Power Pivot>管理数据模型 二、关系图视图将数据加载到Power Pivot 1.打开Power PivotPower Pivot>管理数据模型 2.创建表的关联关系主页>关系图视图
3.连接表拖拽一个表中的关联字段到另一个表中对应的关联字段上，会自动建立连接，连接成功会出现一根连线，连线上会有箭头表示方向，连线上的1和*是指1对多关系 注意：表与表之间只能关联一个字段 三、层次结构&数据透视表&隐式度量值&上下钻取1.层次结构主页>关系图视图>右键表>创建层次结构>取名>将需要的字段拖进创建的层次结构中(注意：级别高的字段要放到级别低的字段的上面)>保存
2.数据透视表Power Pivot>管理>数据透视表>数据透视表>新工作表 或者 现有工作表>确定
将需要的字段拖到相应的列、行、值和筛选所在的位置 注：该数据透视表支持多表关联 3.隐式度量值1）隐式度量值: 通过PowerPivot数据透视表的字段拖放而自动创建的度量值, 不建议 隐式度量值在 Power Pivot>度量值 中是看不到的 2）查看隐式度量值：Power Pivot>管理>高级>显示隐式度量值 3）删除隐式度量值：Power Pivot>管理>高级>显示隐式度量值 >选中要删除的隐式度量值>右键>删除>保存 4）创建度量值：Power Pivot>度量值>新建度量值>表名：创建在哪个表中；度量值名称：自定义取名；公式：根据需求填写公式计算；类型：显示格式>确定 4.上下钻取向上钻取: 从细(小)的维度到粗(大)的维度分析 向下钻取: 从粗(大)的维度到细(小)的维度分析 操作：选中数据透视表>向上钻取 或者 向下钻取 四、度量值&KPI1.度量值结合可视化图表或数据透视表才会执行运算,而计算列后台自动计算(可关闭自动计算) 创建度量值：Power Pivot>度量值>新建度量值>表名：创建在哪个表中；度量值名称：自定义取名；公式：根据需求填写公式计算；类型：显示格式>确定
2.KPI1）创建KPI：Power Pivot>KPI>新建KPI>销售额定义目标值为25000000 2）将KPI添加到数据透视表：选中数据透视表>将创建的KPI拖到相应的位置(可以根据需求对KPI的值选择勾选)
五、DAX公式1.DAX是什么1）Data Analysis Expressions，即数据分析表达式，是一门函数语言，用于数据统计，返回一个或多个值； 2）它是函数、运算符和常量的集合，简单来说就是一个计算公式，通过DAX创建的计算公式称为度量值或计算列； 3）DAX被称为移动公式，解放写代码的双手，让数据分析效率实现质的飞跃，比传统Excel、VBA更加灵活。 2.筛选器上下文&行上下文&数据模型布局上下文：指标计算的筛选条件(计算口径) 1）筛选器上下文 2）行上下文
3）数据模型布局 （1）星型模型
（2）雪花模型
3.对象引用&运算符1）对象引用
2）运算符 （1）运算符优先级
注：可以使用括号控制计算顺序 （2）运算符示例
六、函数1.CALCULATE函数(带着漏斗的计算器)CALCULATE：聚合函数+筛选器函数，被称为带着漏斗的计算器（既能筛选也能计算） 例如：=CALCULATE( SUM('销售表'[金额]), '地区表'[区域]='北区')) 第一个参数为几何函数，后面的参数为筛选条件可以有多个 2.SUMX迭代函数SUMX：按指定的逻辑先计算每行的结果值，再对每行的结果值求和。AVERAGEX、COUNTX、MAXX、MINX、PRODUCTX均属于迭代函数 例如：=SUMX('销售表', '销售表'[单价]*'销售表'[数量]) 第一个参数为表，第二个参数为计算表达式 3.FILTER函数FILTER：筛选器函数，按指定条件筛选表数据，CALCULATE+FILTER 样式：创建专用度量值，用于统计某产品的销售额 例如：=CALCULATE( SUM('销售表'[金额]), FILTER('产品表', [产品大类]="自行车")) FILTER 的 第一个参数为表，第二个参数为筛选条件 4.RELATED函数1）RELATED：关系函数，将维度表的指定列关联至事实表 （1）【维度表】：一表（查找表），【事实表】：多表（明细表） （2）通常情况下【维度表】字段用于分组，【事实表】字段用于聚合计算，所以不能以【事实表】字段为分组，对【维度表】字段进行聚合计算 （3）如果将【事实表】字段关联至【维度表】，那么可以间接地实现：以【事实表】字段为分组，对【维度表】字段进行聚合计算 2）判断对错，直接实现是否可行 （1）以【维度表】字段为分组，对【事实表】字段进行聚合计算：可行 （2）以【维度表】字段为分组，对【维度表】字段进行聚合计算，两者为同一张表：可行 （3）以【事实表】字段为分组，对【事实表】字段进行聚合计算，两者为同一张表：可行 （4）以【事实表】字段为分组，对【维度表】字段进行聚合计算：不可行 3）例如：求已售产品计数 已售产品计数：【销售表】的【产品名称】列去重计数 =DISTINCTCOUNT('销售表'[产品名称]) 将产品表的产品大类关联到销售表中： 在销售表中新建字段产品大类>=RELATED('产品表'[产品大类])>回车 5.RELATEDTABLE函数RELATEDTABLE：关系函数，将事实表的指定列关联至维度表，SUMX+RELATEDTABLE 例如：将【销售表】的【数量】列关联至【地区表】 地区表添加列名为数量的新字段>=SUMX(RELATEDTABLE('销售表'), '销售表'[数量])>回车 6.ALL筛选器函数ALL：筛选器函数，清除指定表（或列）的筛选功能 例如：求各区域各产品的销售额占销售总额的比例 销售总额 =CALCULATE(SUM('销售表'[金额]), ALL('地区表'), ALL('产品表')) ALL第一个参数为表名或列名，即要删除哪个表或哪个列的筛选条件 为了防止分母出现零的情况，使用安全除法DIVIDE函数 销售总额占比=DIVIDE(SUM('销售表'[金额]), [销售总额]) 7.ALLEXCEPT函数ALLEXCEPT：筛选器函数，除了表指定的列之外，清除其他列的筛选功能 例如： 1）以区域分组，求各产品的销售额占比 保留区域筛选功能,删除产品大类筛选功能 销售总额（区域）=CALCULATE(SUM('销售表'[金额]), ALLEXCEPT('地区表', '地区表'[区域]), ALL('产品表')) ALLEXCEPT的第一个参数为表名，第二个参数为字段名 2）以产品分组，求各区域的销售额占比 保留产品大类筛选功能,删除区域筛选功能 销售总额（产品）=CALCULATE(SUM('销售表'[金额]), ALLEXCEPT('产品表', '产品表'[产品大类]), ALL('地区表')) 8.ALLSELECTED函数ALLSELECTED：筛选器函数，清除指定表（或列）的筛选功能，保留其他表（或列）的筛选功能 概念：绝对总计（固定不变）与相对总计（根据筛选而变化） 例： 销售总计(绝对) = CALCULATE(SUM('销售表'[金额]), ALL('产品表'), ALL('地区表')) 销售总计(相对) = CALCULATE(SUM('销售表'[金额]), ALLSELECTED('产品表'[产品小类])) 9.CALENDARCALENDAR：日期和时间函数，快速生成日历表的函数（Excel BI暂时不支持，PowerBI支持） Power Pivot>管理>设计>日期表>新建表
10.USERELATIONSHIPUSERELATIONSHIP：关系函数，CALCULATE+USERELATIONSHIP，按指定的表关联关系来统计某项指标 例如：求不同时间口径的订单数 订单数(下单日期) = CALCULATE(DISTINCTCOUNT([订单编号])，USERELATIONSHIP('Calendar'[Date]，'销售表'[下单日期])) 订单数(实际送货日期) = CALCULATE(DISTINCTCOUNT([订单编号])，USERELATIONSHIP('Calendar'[Date]，'销售表'[实际送货日期])) 11.同比环比定比时间智能函数(筛选器函数)：DATEADD、DATESBETWEEN，CALCULATE+时间智能函数 环比，例如今年6月与今年5月的比较 同比，例如今年6月与去年6月的比较 定比，例如今年1月-12月分别与今年1月的比较 DATEADD() 第一个参数为日历表的日期字段，第二个参数为间隔数量可理解为偏移时间单位的数量，往过去偏移是负数，往未来偏移是整数，第三个参数为单位即偏移数量的单位 DATESBETWEEN() 第一个参数为日历表的日期，第二个参数为开始日期，第三个参数为结束日期 STARTOFYEAR() 返回当年年初的日期即对应年份的1月1日 ENDOFMONTH() 返回对应月份的月末日期 例如：求环比增长率、同比增长率、定比增长率 订单数(上月) = CALCULATE(DISTINCTCOUNT([订单编号])，DATEADD('Calendar'[Date], -1，MONTH)) 订单数(去年) = CALCULATE(DISTINCTCOUNT([订单编号])，DATEADD('Calendar'[Date], -1，YEAR)) 订单数(一月) = CALCULATE( DISTINCTCOUNT([订单编号], DATESBETWEEN( 'Calendar'[Date], STARTOFYEAR('Calendar'[Date]), ENDOFMONTH(STARTOFYEAR('Calendar'[Date])) ) ) 环比增长率：=DIVIDE(DISTINCTCOUNT([订单编号])，[订单数(上月)]) - 1 同比增长率：=DIVIDE(DISTINCTCOUNT([订单编号])，[订单数(去年)]) - 1 定比增长率：=DIVIDE(DISTINCTCOUNT([订单编号])，[订单数(一月)]) - 1 12.时间累计时间智能函数：TOTALMTD、TOTALQTD、TOTALYTD 例如：求月度累计订单数、季度累计订单数、年度累计订单数 月度累计：=TOTALMTD(DISTINCTCOUNT([订单编号]), 'Calendar'[Date]) 季度累计：=TOTALQTD(DISTINCTCOUNT([订单编号]), 'Calendar'[Date]) 年度累计：=TOTALYTD(DISTINCTCOUNT([订单编号]), 'Calendar'[Date]) 13.VALUES函数VALUES：表操作函数，返回由一列组成，且不含重复值的表，CALCULATE+FILTER+VALUES 例如：求各区域销售额大于200万的省份数量 =CALCULATE( DISTINCTCOUNT('地区表'[省份]), FILTER(VALUES('地区表'[省份]), [销售额] > 2000000) ) 14.HASONEVALUE函数HASONEVALUE：IF+HASONEVALUE+BLANK，解决某些度量值无法求总计的报错问题 样式：求截止某个时间点的近7日订单数 订单数：=DISTINCTCOUNT([订单编号]) 近七日订单数：=CALCULATE([订单数], DATESBETWEEN( 'Calendar'[Date], DATEADD('Calendar'[Date], -6, DAY), DATEADD('Calendar'[Date], 0, DAY) ) ) 近七日订单数(禁用总计)：=IF(HASONEVALUE('Calendar'[Date]), [近7日订单数], BLANK()) 15.EARLIER函数&RANKX函数1）EARLIER函数 可理解为：EARLIER=当前行 EARLIER：迭代函数，逐行执行计算，COUNTROWS+FILTER+EARLIER计算排名 例如：【地区表】添加列【排名】，按【数量】计算各省的排名 =COUNTROWS(FILTER('地区'[数量] >= EARLIER('地区表'[数量]))) 即：计算数量字段中大于当前行的数量的行数 2）RANKX函数 第一个参数为表名，第二个参数为表达式，第三个参数为可选值一般不用写，第四个参数为排序方式其中ASC为升序DESC为降序，排序规则Dense(密集排序)排名依次排列，Skip(跳过排序)如果有并列会跳过下一个排名数，如有2个并列排名为5的则会跳过排序数6 例如：【地区表】添加列【排名】，按【数量】计算各省的排名 =RANKX('地区表'，'地区表'[数量]，，DESC，Dense) 16.VAR+RETURN函数VAR+RETURN：定义变量，对于复杂的度量值，增强可读性 例如：求环比增长率 = VAR cnt = DISTINCTCOUNT('销售表'[订单编号]) // 本月订单数 VAR cnt_lm = CALCULATE(DISTINCTCOUNT('销售表'[订单编号]), DATEADD('Calendar'[Date], -1, MONTH)) // 上月订单数 RETURN DIVIDE(cnt, cnt_lm) - 1 |
|
|
来自： zonge > 《Power Pivot》
