透视分析,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》