精华内容
下载资源
问答
  • 目录 RELATED函数 RELATEDTABLE RANK.EQ COUNTROWS CALCULATE DIVIDE ...RELATED函数 ...类似于Excel中的Vlookup,Power Query中的合并查询RELATED 函数要求当前表与包含相关信息的表之...

    目录

     

    RELATED函数

    RELATEDTABLE

    RANK.EQ

    COUNTROWS

    CALCULATE

    DIVIDE


     

    image_1cquddb8dj36h0bktui6916el9.png-60.8kB

     

    RELATED函数

    RELATED(ColumnName)
    RELATED(返回的对应值的列名)
    →从其他表返回相关值
    类似于Excel中的Vlookup,Power Query中的合并查询
    RELATED 函数要求当前表与包含相关信息的表之间存在关系,需要指定包含所需数据的列,该函数将通过现有的多对一关系从相关表中的指定列中提取值。如果不存在关系,则必须创建关系。

    如下图示:
    表示此表区域列返回的是“省份区域”表中的“区域”列的值

    image_1cqqt5c9bcn20k1s871gt7h3v9.png-326.2kB

    RELATEDTABLE

    RELATEDTABLE(TableName)
    →返回已筛选的相关表,以便该表只包含相关行

    image_1cqug5ij515ovf3sa3pepvmlm.png-69.6kB


    需要选取的表格中仅有一列数据,一般嵌套在其他函数中搭配使用

    image_1cquhs6e911v5gvajvf1hb0qtb9.png-20.4kB

    RANK.EQ

    RANK.EQ(Value,ColumnName,[Order])
    RANK.EQ(值,列,排序方式)
    Order值1为升序排列,数值越小排名越靠前;0为降序排列,数值越大排名越靠前
    →返回值在一个由值构成的列中的排名。如果多个值有相同排名,则返回该组值的最高排名

    image_1cqui7o301dpm1vo31ngucbq1ms8m.png-31.8kB

    COUNTROWS

    COUNTROWS(Table)
    →对一个表中的行数计数

    image_1cquifulm1n0a1c0cs0v1lu016e513.png-39.5kB

    CALCULATE

    CALCULATE(,,…)
    CALCULATE(要进行计算的度量值,条件)
    *1类似于Excel中的sumif,但是此公式仅可对Power pivot计算区域中的度量值做计算,不可对数据区数据做计算。
    *2计算出来的值,在数据透视表中,若以公式中的条件做切片器,筛选时数据不会随贴片器筛选而变化。

    image_1cquirq2tsb71vab1feptaq10ql1g.png-38.9kB

    DIVIDE

    DIVIDE(, [,])
    DIVIDE(分子,分母,(可选)被零除而导致错误时返回的值。如果没有提供,则默认值为 BLANK()。)
    →安全除法,执行除法运算,并在被 0 除时返回备用结果或 BLANK()。

    image_1cquivedr1b0d1ltqtf1akc149b1t.png-29.7kB

    展开全文
  • PowerPivot的DAX函数实战

    千次阅读 2018-10-23 17:46:53
    针对PowerPivot的DAX函数参考 一、筛选函数 1、ALL函数:返回表中的所有行货返回列中的所有值,同时忽略可能已应用的任何筛选器,此函数可用于清除筛选器对表中的所有行创建计算,返回删除了筛选器的表或列的值 ...

    针对PowerPivot的DAX函数参考

    一、筛选函数

    1、ALL函数:返回表中的所有行货返回列中的所有值,同时忽略可能已应用的任何筛选器,此函数可用于清除筛选器对表中的所有行创建计算,返回删除了筛选器的表或列的值

    <span style="color:#000000">语法:ALL({<table> | <column>[, <column>[, <column>[,…]]]})</span>

    如:创建一个《度量值》

    订单额度 = SUMX(ALL('订单表'),'订单表'[amount])

    blob.png

     

    虽然X轴使用日期来统计,显示的依然是总额度,在不使用ALL函数的一个度量值

    订单额度2 = SUMX('订单表','订单表'[amount])

    blob.png

    没有使用ALL时候,时间筛选器就生效了,显示各日的总额。实际应用中可能会分析日订单和总额的比率:

    计算日订单和总额的比率 = sumx('订单表','订单表'[amount])/sumx(ALL('订单表'),'订单表'[amount])

     

    ALL (Column[, Column[, …]]) 删除表中指定列的所有筛选器,表中针对其他列的所有筛选器仍应用,创建一个度量值:

    订单额度 = SUMX('订单表','订单表'[amount])/CALCULATE(SUM('订单表'[amount]),ALL('订单表'[create_time],'订单表'[cancel_rent_time]))

     

    ALLEXCEPT(Table, Column1 [,Column2]...):删除表中所有上下文筛选器,但已应用于制定列的筛选器除外,创建一个度量值:

    订单额度 = SUMX('订单表','订单表'[amount])/SUMX(ALLEXCEPT('订单表','订单表'[create_time],'订单表'[rent_pay_time],'订单表'[deposit_withdraw_time]), '订单表'[amount])

     

    ALLSELECTED:选取需要字段过滤其他,只能添加一个参数,同时忽略选取字段的筛选器,其他已应用的筛选器仍然生效,选取的时间仍然创建度量值的表,创建一个度量值:

    订单额度 = SUMX('订单表','订单表'[amount])/CALCULATE(SUMX('订单表','订单表'[amount]),ALLSELECTED('订单表'[create_time]))

    blob.png

    对比没有allselectd的情况下的筛选

    blob.png

     

    ALLNOBLANKROW函数:从关系的父表中,返回除空白行之外的所有行,或某一列中除空白行之外的所有非重复值,并且忽略可能存在所有上下文的筛选器。

    <span style="color:#000000">语法:ALLNOBLANKROW(<table>|<column>)</span>

     

    若选table要删除其所有上下文筛选器的表,列同理,创建一个度量值

    订单额度222 = CALCULATE(SUMX('订单表','订单表'[amount]),ALLNOBLANKROW('订单表'))

    Datekey是张时间的维度表和和订单表相关联

    ,选自己的表的时间,列出当前时间,

    blob.png

     

    使用时间维度表,维度表所有的时间都会显示一个同一个值

    blob.png

    2、CALCULATE函数:计算由指定筛选器修改的上下文中的表达式,作为表达式的结果值返回。

    语法:CALCULATE(<expression>,<filter1>,<filter2>…)

    expression要进行计算的表达式

    filter1…N定义筛选器的布尔表达式或表表大师的逗号分隔的列表

    创建一个度量值:

    <span style="color:#000000">度量值1 = CALCULATE(SUMx('订单表','订单表'[amount]),'订单表'[status]=200)</span>

     

     

    限制:不能引用度量值、不能使用CALCULATE嵌套、不能使用扫描表或返回任何函数及聚合函数

    订单额度 = CALCULATE(SUM('订单表'[amount]),(ALL('订单表')))

    blob.png

    CALCULATETABLE函数:在由给定筛选修改的上下文中计算表表达式,返回包含值得表,在BPI中的建模点新建表,此时不能使用新建度量值。

    <span style="color:#000000">语法: CALCULATETABLE(<expression>,<filter1>,<filter2>,…)</span>
    <span style="color:#000000">度量值cal = CALCULATETABLE('订单表','订单表'[status]=100)</span>

     

    新度量值 = SUMX(CALCULATETABLE('订单表','PowerBi 时间维度表'[DayOfMonthFull]="18"),'订单表'[amount])

    此时嵌套在聚合函数里,返回值的列表

     blob.png

     

    此时说明表达式返回表的多列值,只是根据条件过滤不匹配status=100de

     

    3、DISTINCT:返回一列构成的一个表,该表包含来自指定列的非重复值,一般配合其他聚合函数来使用

    语法:DISTINCT(<column>)

    度量值cal = COUNTROWS(DISTINCT('订单表'[status]))

     

    4、EARLIER函数:返回提及的列的外部计算传递中指定列的当前值(在想要使用某个值作为输入并且基于该输入进行计算的嵌套计算中)

    官档案例

    <span style="color:#000000">= COUNTROWS(FILTER(ProductSubcategory, EARLIER(ProductSubcategory[TotalSubcategorySales])<ProductSubcategory[TotalSubcategorySales]))+1</span>

     

     

    5、EARLIEST函数:返回指定列的外部计算传递中制定列的当前值,与4类似

    <span style="color:#000000">6、FILTER函数:返回表示另一个表达式的子集的表,只包含筛选行的表返回值</span>
    <span style="color:#000000">语法:FILTER(<table>,<filter>)</span>
    <span style="color:#000000">参数:</span>
    <span style="color:#000000">table要筛选的表,表还可以是产生的结果集是表的表达式</span>
    <span style="color:#000000">        Filter为该表的每一行计算的布尔表达式</span>
    <span style="color:#000000">   FILTERS:返回直接作为筛选器应用于columnName的值</span>
    <span style="color:#000000">   HASONEFILTER:当columnName上的直接筛选值得数目为一个值,返回TRUE否则返回FALSE</span>

     

         HASONEVALUE:已将columnName的上下文筛选为只剩下一个非重复值时返回TRUE,否则FALSE

     

    7、RELATED函数:从另一个表返回相关值,与当前行相关的单个值

    <span style="color:#000000">语法:RELATED(<column>)</span>
    <span style="color:#000000">##</span>

     

    RELATED 函数要求当前表与包含相关信息的表之间存在关系。您需要指定包含所需数据的列,该函数将通过现有的多对一关系从相关表中的指定列中提取值

    <span style="color:#000000">FILTER_RELATED_1 = SUMX(FILTER('订单表',RELATED('订单表状态维度表'[status_id])=101),'订单表'[amount])</span>

     

    blob.png

    <span style="color:#000000">通过通过计算累计101值得累计和</span>

     

    8、RELATEDTABLE函数:由给定筛选器修改的上下文中计算表表达式,返回包含值得表

    <span style="color:#000000">语法: RELATEDTABLE(<expression>,<filter1>,<filter2>,…)</span>
    <span style="color:#000000">注释</span>

     

    RELATEDTETABLE 函数将更改筛选数据的上下文,并在您指定的新上下文中计算表达式。对于在筛选器参数中使用的每一列,将删除该列上的现有筛选器,改为应用在筛选器参数中使用的筛选器

    <span style="color:#000000">###此函数是 CALCULATETABLE 函数的同义词</span>

     

    9、VALUES函数:返回由一列构成的一个表,包含来自制定列的非重复值。

    语法:values(<column>)

    注释

    当您在已筛选的上下文(例如数据透视表)中使用 VALUES 函数时,VALUES 返回的唯一值会受到筛选器的影响。例如,如果您按 Region 进行筛选,并且返回针对 City 的值列表,则该列表将只包括筛选器允许的区域中的那些城市。若要返回所有城市,而不管现有筛选器的情况如何,您必须使用 ALL 函数从表中删除筛选器。第二个示例演示如何将 ALL 与VALUES 一起使用。

    VALUES_1 = COUNTROWS(VALUES('订单表'[id] ))

    blob.png

     

    统计函数(聚合函数):对值得聚合运算,在筛选函数中已经应用部分,其他类似

     

    <span style="color:#000000">AVERAGE(<column>)</span>
    <span style="color:#000000">AVERAGE(<column>):返回列中值的平均值(算术平均值)。处理文本和非数字值</span>
    <span style="color:#000000">AVERAGEX(<table>,<expression>):计算对表进行求值的一组表达式的平均值(算术平均值)</span>
    <span style="color:#000000">COUNTA (<column>)函数计算列中不为空的单元的数目。它不仅对包含数值的行进行计数,还对包含非空白值(包括文本、日期和逻辑值)的行进行计数</span>
    <span style="color:#000000">COUNTAX 函数用于在对表计算表达式的结果时统计非空结果数。即,它的作用与 COUNTA 函数类似,但它用于对表中所有行进行循环访问,并统计指定表达式计算为非空结果的行数</span>
    <span style="color:#000000">COUNTAX(<table>,<expression>)</span>
    <span style="color:#000000">COUNTBLANK(<column>)  计算列中空白单元的数目</span>
    <span style="color:#000000"> 
    </span>
    <span style="color:#000000">COUNTROWS函数:计算制定表中的行数,或计算表达式定义的表中的行数</span>
    <span style="color:#000000">语法:COUNTROWS(<TABLE>)</span>
    <span style="color:#000000">COUNTX(<table>,<expression>)在对表计算表达式的结果时,计算包含数字或者计算结果为数字的表达式的行的数目</span>
    <span style="color:#000000">MAX</span>
    <span style="color:#000000">MAXA 返回列中的最大值。逻辑值和空白被计算在内MAXA(<column>)  MINA</span>
    <span style="color:#000000">MAXX为表的每一行计算表达式,并且返回最大的数值MAXX(<table>,<expression>) MINX</span>

     

     

     

    <span style="color:#000000">逻辑函数:</span>
    <span style="color:#000000">AND函数:检查是否两个参数均为TRUE</span>
    <span style="color:#000000">语法:AND(<LOGICAL1>,<LOGICAL2>)</span>

     

    注释

    DAX 中的 AND 函数只接受两 (2) 个参数。如果您需要对多个表达式执行 AND 运算,则可以创建一系列计算;但更好的选择是,使用连接运算符 (&&) 在一个更简单的表达式中连接所有这些列

    <span style="color:#000000"> 
    </span>
    <span style="color:#000000">FALSE函数:返回逻辑值FALSE</span>
    <span style="color:#000000">语法:FALSE()</span>
    <span style="color:#000000"> 
    </span>
    <span style="color:#000000">IF函数:检查是否满足作为第一个参数提供的条件,满足为TRUE,否则为FALSE</span>
    <span style="color:#000000">语法: IF(logical_test>,<value_if_true>, value_if_false)</span>

     

    参数

    术语

    定义

    logical_test

    计算结果可以为 TRUE 或 FALSE 的任何值或表达式。

    value_if_true

    在逻辑测试为 TRUE 时返回的值。如果省略,则返回 TRUE。

    value_if_false

    在逻辑测试为 FALSE 时返回的值。如果省略,则返回 FALSE。

    <span style="color:#000000">=IF([Calls]<200,"low",IF([Calls]<300,"medium","high"))</span>
    <span style="color:#000000">EGG:</span>
    <span style="color:#000000">新增列IF = IF([status]=101, "已付押金",IF([status]=200, "租赁中",IF([status]=300, "待付租金",IF([status]=400, "已付租金",IF([status]=500, "未退押金",</span>
    <span style="color:#000000">    IF([status]=600, "交易失败",IF([status]=700,"申诉中" ,"取消"</span>
    <span style="color:#000000">)))))))</span>
    <span style="color:#000000">他的另外一种简洁语法:</span>
    <span style="color:#000000">列SWITCH = SWITCH([status],101,"已付押金",200,"租赁中",300,"待付租金",400,"已付租金",500,"未退押金",600,"交易失败",700,"申诉中","取消")</span>

     

    blob.png

    注释

    如果省略了 value_if_true 或 value_if_false 的值,则 IF 将其视为空字符串值 (""),

    如果在表达式中引用的值是某一列,则 IF 将返回与当前行相对应的值

    IF 函数尝试返回列中的单个数据类型。因此,如果 value_if_true 和 value_if_false 返回的值属于不同的数据类型,则 IF 函数会隐式转换数据类型以便适合列中的这两种值。例如,公式 IF(<condition>,TRUE(),0) 返回由 1 和 0 组成的列,并且可以对结果求和,但公式 IF(<condition>,TRUE(),FALSE()) 只返回逻辑值

    <span style="color:#000000">IFERROR函数:对某一表达式进行计算,并且如果该表达式返回错误则返回指定值,否则返回该表达式本身的值</span>
    <span style="color:#000000">语法:IFERROR(value,value_if_error)##返回值类型要相同</span>
    <span style="color:#000000">=IFERROR(25/0,9999)</span>
    <span style="color:#000000">NOT函数:将FALSE改成TRUE,或者将TRUE改成FALSE</span>
    <span style="color:#000000">语法:NOT(<logical>)</span>
    <span style="color:#000000">OR函数 OR(<logical1>,<logical2>)</span>
    <span style="color:#000000">IF(   OR(   CALCULATE(SUM('ResellerSales_USD'[SalesAmount_USD]), 'ProductSubcategory'[ProductSubcategoryName]="Touring Bikes") > 1000000</span>
    <span style="color:#000000">         ,   CALCULATE(SUM('ResellerSales_USD'[SalesAmount_USD]), 'DateTime'[CalendarYear]=2003) > 2500000</span>
    <span style="color:#000000">         )</span>
    <span style="color:#000000">   , "Circle of Excellence"</span>
    <span style="color:#000000">   , ""</span>
    <span style="color:#000000">   )</span>
    <span style="color:#000000">TRUE函数:  语法:TRUE()</span>

     

    = IF(SUM('InternetSales_USD'[SalesAmount_USD]) >200000, TRUE(), false())

     

     

    <span style="color:#000000"> 
    </span>
    <span style="color:#000000">文本函数:</span>
    <span style="color:#000000">BLAN()   返回空白。</span>
    <span style="color:#000000">CONCATENATE:将两个文本字符串连接成一个文本字符串</span>
    <span style="color:#000000">= CONCATENATE(sumx('订单表','订单表'[amount])/sumx(ALL('订单表'),'订单表'[amount]),"%")</span>
    <span style="color:#000000">EXACT函数:比较字符串,相同为TRUE</span>
    <span style="color:#000000">EXACT(<TEXT1>,<TEXT2>)</span>
    <span style="color:#000000">FIND函数: 返回一个文本字符串在另一文本字符串中的开始位置。FIND 区分大小写。</span>
    <span style="color:#000000">FIND(<find_text, within_text, start_num)</span>

     

    参数

    定义

    find_text

    您要查找的文本。使用双引号(空文本)可以匹配 within_text 中的第一个字符;不允许通配符。

    within_text

    包含要查找的文本的文本。

    start_num

    从其开始进行搜索的字符;如果省略,start_num = 1。within_text 中的第一个字符是字符编号 1。

    <span style="color:#000000">FIXED函数: 将数字舍入到指定的小数位数,并以文本形式返回结果。可以指定以带或不带逗号的形式返回结果</span>
    <span style="color:#000000">将数字舍入到指定的小数位数,并以文本形式返回结果。可以指定以带或不带逗号的形式返回结果</span>

     

    参数

    定义

    number

    要舍入并转换为文本的数字,或包含数字的列。

    decimals

    (可选)小数点右侧的位数;如果省略,则为 2。

    no_commas

    (可选)一个逻辑值:如果为 1,则不在返回的文本中显示逗号;如果为 0 或省略,则在返回的文本中显示逗号。

    <span style="color:#000000">FORMAT函数:根据指定的格式转换为文本</span>
    <span style="color:#000000">FORMAT(<value>, <format_string>)</span>

     

    参数

    术语

    定义

    value

    值或计算结果为单个值的表达式。

    format_string

    具有格式模板的字符串。

    <span style="color:#000000">FORMAT函数的预定义数字格式</span>

     

    下表标识预定义数字格式的名称。可按名称将它们用作 Format 函数的样式参数。

    格式规范

    说明

    "General Number"

    显示不带千位分隔符的数字。

    "Currency"

    显示带千位分隔符的数字(如果适用);显示小数点分隔符右侧两位。输出基于系统区域设置。

    "Fixed"

    小数点分隔符左侧至少显示一位,右侧显示两位。

    "Standard"

    显示带千位分隔符的数字,其中小数点分隔符左侧至少有一位,右侧有两位。

    "Percent"

    将数字乘以 100 后显示,并在紧右侧追加百分号 (%);小数点分隔符右侧总是显示两位。

    "Scientific"

    使用标准的科学记数法,并且提供两个有效位数。

    "Yes/No"

    如果数字为 0,则显示 No;否则显示 Yes。

    "True/False"

    如果数字为 0,则显示 False;否则显示   True。

    "On/Off"

    如果数字为 0,则显示 Off;否则显示 On。

    <span style="color:#000000">
    </span>
    <span style="color:#000000"> 
    </span>
    <span style="color:#000000">FORMAT函数的自定义数字格式</span>

     

    用户定义的数字格式表达式可以包含一至三个部分(各部分之间用分号分隔)。如果 Format 函数的 Style 参数包含预定义的数字格式之一,则只允许有一部分。

    如果您使用

    则结果为

    只有一部分

    格式表达式应用于所有值。

    两部分

    第一部分应用于正值和零;第二部分应用于负值。

    三部分

    第一部分应用于正值,第二部分应用于负值,第三部分应用于零。

    格式规范

    下表标识了可用于创建用户定义的数字格式的字符。

    格式规范

    说明

    显示不带格式的数字。

    0(零字符)

    数字占位符。显示一个数字或一个零。如果表达式在格式字符串中出现零的位置有一个数字,则显示该数字,否则在该位置显示一个零。

    如果数字的位数少于格式表达式中零的个数(不管在小数点的哪一侧),将显示前导零或尾随零。如果小数点分隔符右侧的数字位数多于格式表达式中小数点分隔符右侧的零的个数,将对数字四舍五入,使其小数位数与零的个数一样多。如果小数点分隔符左侧的数字位数多于格式表达式中小数点分隔符左侧的零的个数,则将显示多出的位数而不作任何修改。

    #

    数字占位符。显示一个数字或不显示任何内容。如果表达式在格式字符串中出现 # 字符的位置有一个数字,则显示该数字,否则该位置什么也不显示。

    除了当数字的位数少于格式表达式中小数点分隔符任意一侧的 # 字符数时不显示前导零或尾随零外,该符号的作用类似于零 (0) 数字占位符。

    .(圆点字符)

    小数点占位符。小数点占位符确定在小数点分隔符左侧和右侧显示几位数。如果格式表达式中该符号的左侧只包含 # 字符,则小于 1 的数字以小数点分隔符开头。若要显示随小数显示的前导零,请使用零作为小数点分隔符左侧的第一个数字占位符。在某些区域设置中,用逗号作为小数点分隔符。在格式化输出中用作小数点占位符的实际字符取决于系统所识别的数字格式。因此,在您的格式中应将句点用作小数点占位符,即使您位于使用逗号作为小数点占位符的区域设置中。格式化字符串将以适合区域设置的正确格式显示。

    %

    百分比占位符。将表达式乘以 100。在格式字符串中出现百分比字符的位置插入百分比字符 (%)。

    ,(逗号字符)

    千位分隔符。千位分隔符将在小数点分隔符左侧具有四位或更多位数的数字中的千位和百位分隔开。如果格式包含一个由数字占位符(0 或 #)包围的千位分隔符,则指定千位分隔符的标准用法。

    一个千位分隔符紧挨小数点分隔符的左侧(无论是否指定小数)或者作为字符串中最右侧的字符表示“通过除以 1000 来将数字按比例减小,并按需要四舍五入”。小于 1,000 但大于或等于   500 的数字显示为 1,小于 500 的数字显示为 0。此位置有两个相邻千位分隔符表示按一百万倍的比例因子缩小,并且每增加一个分隔符表示另外缩小 1,000 倍。

    多个分隔符出现在紧挨小数点分隔符左侧或字符串最右侧位置被视为指定使用千位分隔符。在某些区域设置中,用句点作为千位分隔符。在格式化输出中用作千位分隔符的实际字符取决于您的系统所识别的数字格式。因此,在您的格式中应将逗号用作千位分隔符,即使您位于使用句点作为千位分隔符的区域设置中。格式化字符串将以适合区域设置的正确格式显示。

    例如,考虑以下三个格式字符串:

    “#,0.”,该字符串使用千位分隔符将数字一亿的格式设置为字符串“100,000,000”。

    “#0,.”,该字符串按 1000 的因子进行缩小,将数字一亿的格式设置为字符串“100000”。

    “#,0,.”该字符串使用千位分隔符和按 1000 的因子进行缩小,将数字一亿的格式设置为字符串“100,000”。

    :(冒号字符)

    时间分隔符。在某些区域设置中,可能用其他字符表示时间分隔符。在设置时间值的格式时,时间分隔符用于分隔小时、分钟和秒。在格式化输出中用作时间分隔符的实际字符取决于您的系统设置。

    /(正斜杠字符)

    日期分隔符。在某些区域设置中,可能用其他字符表示日期分隔符。在设置日期值的格式时,日期分隔符用于分隔年、月和日。在格式化输出中用作日期分隔符的实际字符取决于您的系统设置。

    E-、E+、e-、e+

    科学记数法格式。如果格式表达式在 E-、E+、e- 或 e+ 的左侧至少包含一个数字占位符(0 或 #),则以科学记数法格式显示数字,并在数字与其指数之间插入 E 或 e。左侧的数字占位符的个数决定了指数中的数字个数。使用 E- 或 e- 可在负指数的后面放置一个减号。使用 E+ 或 e+ 可在负指数的后面放置一个减号,在正指数的后面放置一个加号。还必须在该符号右侧包括数字占位符以获取正确的格式。

    - + $ ( )

    文字字符。这些字符将完全按格式字符串中所键入的形式显示。若要显示所列字符以外的其他字符,请在该字符前加上一个反斜杠 (\) 或将该字符放在双引号 (" ") 中。

    \(反斜杠字符)

    显示格式字符串中的下一个字符。若要将具有特殊含义的字符显示为文字字符,请在该字符前加上一个反斜杠 (\)。反斜杠本身不显示。使用反斜杠与将下一个字符放在双引号中的作用是相同的。若要显示反斜杠,请使用两个反斜杠 (\\)。

    不能显示为文字字符的字符示例为日期格式字符和时间格式字符(a、c、d、h、m、n、p、q、s、t、w、y、/ 和 :)、数字格式字符(#、0、%、E、e、逗号和句点)和字符串格式字符(@、&、<、> 和 !)。

    "ABC"

    显示双引号 (" ") 里面的字符串。若要将字符串包含在代码内的样式参数中,必须将文本放在 Chr (34) 之间(34 为引号 (") 的字符代码)。

    下表包含用于表示数字的一些示例格式表达式。(所有这些示例均假设您的系统区域设置为“英语 - 美国”)第一列包含 Format 函数的格式字符串;如果格式化的数据具有在列标题中给定的值,则其他列包含生成的输出结果。

    格式(样式)

    “5”格式设置为

    “-5”格式设置为

    “0.5”格式设置为

    “0”格式设置为

    零长度字符串 ("")

    5

    -5

    0.5

    0

    0

    5

    -5

    1

    0

    0.00

    5.00

    -5.00

    0.50

    0.00

    #,##0

    5

    -5

    1

    0

    $#,##0;($#,##0)

    $5

    ($5)

    $1

    $0

    $#,##0.00;($#,##0.00)

    $5.00

    ($5.00)

    $0.50

    $0.00

    0%

    500%

    -500%

    50%

    0%

    0.00%

    500.00%

    -500.00%

    50.00%

    0.00%

    0.00E+00

    5.00E+00

    -5.00E+00

    5.00E-01

    0.00E+00

    0.00E-00

    5.00E00

    -5.00E00

    5.00E-01

    0.00E00

    "$#,##0;;\Z\e\r\o"

    $5

    $-5

    $1

    注释

    如果包含两个连续的分号,则缺少的部分用正值的格式显示。

    <span style="color:#000000">预定义日期和时间格式:</span>

     

    下表标识预定义的日期和时间格式的名称。如果您使用这些预定义的字符串之外的字符串,它们将作为自定义日期和时间格式来解释。

    格式规范

    说明

    "General Date"

    显示日期和/或时间。例如 3/12/2008 11:07:31 AM。日期显示由您的应用程序的当前区域性值确定。

    "Long Date" 或者"Medium   Date"

    根据您的当前区域性的长日期格式显示日期。例如,2008 年 3 月 12 日,星期三。

    "Short Date"

    使用您的当前区域性的短日期格式显示日期。例如 3/12/2008。

    "Long Time" 或者"Medium   Time"

    使用您的当前区域性的长时间格式显示时间;通常包括小时、分钟和秒。例如,11:07:31 AM。

    "Short Time"

    使用您的当前区域性的短时间格式显示时间。例如,11:07 AM。

    <span style="color:#000000">FORMAT函数自定义日期和时间格式:</span>

     

    下表显示可用于创建用户定义的日期/时间格式的字符。

    格式规范

    说明

    (:)

    时间分隔符。在某些区域设置中,可能用其他字符表示时间分隔符。在设置时间值的格式时,时间分隔符用于分隔小时、分钟和秒。在格式化输出中用作时间分隔符的实际字符取决于您应用程序的当前区域性值。

    (/)

    日期分隔符。在某些区域设置中,可能用其他字符表示日期分隔符。在设置日期值的格式时,日期分隔符用于分隔年、月和日。在格式化输出中用作日期分隔符的实际字符取决于您应用程序的当前区域性。

    (%)

    用于指示应以单个字母格式读取后面的字符,而不考虑任何尾随字母。此外,还用于指示以用户定义的格式读取单个字母格式。有关其他详细信息,请参阅以下内容。

    d

    将天显示为不带前导零的数字(例如,1)。如果这是用户定义的数字格式中的唯一字符,则使用 %d。

    dd

    将天显示为带一个前导零的数字(例如,01)。

    ddd

    将天显示为缩写(例如,Sun)。

    dddd

    将天显示为全名(例如,Sunday)。

    M

    将月显示为不带前导零的数字(例如,一月表示为 1)。如果这是用户定义的数字格式中的唯一字符,则使用 %M。

    MM

    将月显示为带一个前导零的数字(例如,01/12/01)。

    MMM

    将月显示为缩写(例如,Jan)。

    MMMM

    将月显示为完整的月份名称(例如,January)。

    gg

    显示时期/时代字符串(例如,A.D.)。

    h

    使用 12 小时制时钟将小时显示为不带前导零的数字(例如,1:15:15 PM)。如果这是用户定义的数字格式中的唯一字符,则使用 %h。

    hh

    使用 12 小时制时钟将小时显示为带前导零的数字(例如,01:15:15 PM)。

    H

    使用 24 小时制时钟将小时显示为不带前导零的数字(例如,1:15:15)。如果这是用户定义的数字格式中的唯一字符,则使用 %H。

    HH

    使用 24 小时制时钟将小时显示为带前导零的数字(例如,01:15:15)。

    m

    将分钟显示为不带前导零的数字(例如,12:1:15)。如果这是用户定义的数字格式中的唯一字符,则使用 %m。

    mm

    将分钟显示为带前导零的数字(例如,12:01:15)。

    s

    将秒显示为不带前导零的数字(例如,12:15:5)。如果这是用户定义的数字格式中的唯一字符,则使用 %s。

    ss

    将秒显示为带前导零的数字(例如,12:15:05)。

    f

    显示秒的小数部分。例如,ff 显示百分之一秒,而 ffff 显示万分之一秒。在用户定义的格式中最多可以使用 7 个 f 符号。如果这是用户定义的数字格式中的唯一字符,则使用 %f。

    t

    使用 12 小时制时钟,对于中午之前的任何小时都显示大写字母 A;对于中午与 11:59 P.M 之间的任何小时都显示大写字母 P。如果这是用户定义的数字格式中的唯一字符,则使用 %t。

    tt

    对于使用 12 小时制时钟的区域设置,对中午之前的任何小时都显示大写字母 AM;对于中午与 11:59 P.M 之间的任何小时都显示 PM。

    对于使用 24 小时制时钟的区域设置,不显示任何内容。

    y

    将年显示为不带前导零的数字 (0-9)。如果这是用户定义的数字格式中的唯一字符,则使用 %y。

    yy

    以带一个前导零的两位数字格式显示年(如果适用)。

    yyy

    以四位数字格式显示年。

    yyyy

    以四位数字格式显示年。

    z

    显示不带前导零的时区偏移量(例如,-8)。如果这是用户定义的数字格式中的唯一字符,则使用 %z。

    zz

    显示带一个前导零的时区偏移量(例如,-08)

    zzz

    显示完整的时区偏移量(例如,-08:00)

    注释

    格式设置字符串区分大小写。使用不同的大小写形式,可以获取不同的格式。例如,当使用字符串“D”格式化日期值时,可以获得采用长格式的日期(根据您的当前区域设置)。但是,如果将此字母更改为小写“d”,则可以获得采用短格式的日期。此外,如果目标格式与任何定义的格式字符串的大小写都不匹配,则可能出现意外结果或错误。

    日期/时间格式使用当前用户区域设置来确定字符串的最终格式。例如,若要使用下列格式字符串“M/d/yyyy”来设置日期 1995 年 3 月 18 日,如果用户区域设置为美国 (en-us),则结果为“3/18/1995”;但如果用户区域设置为德国 (de-de),则结果为“18.03.1995”

    <span style="color:#000000">LEFT函数:从文本字符串的开头返回指定数据的字符--- RIGHT(<text>, <num_chars>)</span>
    <span style="color:#000000"> 
    </span>
    <span style="color:#000000">LEFT(<text>, <num_chars>)</span>
    <span style="color:#000000">LEN(<TEXT>)</span>
    <span style="color:#000000">LOWER(<TEXT>)      ….UPPER</span>

     

    REPLACE 将基于您指定的字符数,用不同的文本字符串替换文本字符串的一部分。

    语法

    <span style="color:#000000">REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)</span>

     

    REPT:重复给定次数的文本。使用 REPT 可用一个文本字符串的许多实例填充单元格。

    语法

    <span style="color:#000000">REPT(<text>, <num_times>)</span>
    <span style="color:#000000">SEARCH:返回最先找到特定字符或文字字符串的位置的字符编号</span>

     

    语法

    <span style="color:#000000">SEARCH(<find_text>, <within_text>, [start_num])</span>
    <span style="color:#000000">SUBSTITUTE:用文本字符中的新文本替换现有文本。</span>

     

    语法

    <span style="color:#000000">SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>)</span>
    <span style="color:#000000">TRIM:从文本删除两个词之间除了但哥空格外的所有空格</span>

     

    语法

    <span style="color:#000000">TRIM(<text>)</span>
    <span style="color:#000000">VALUE:将表示数字的文本字符串转为数字</span>
    展开全文
  • 5.2 power pivot逻辑函数

    千次阅读 2018-09-03 20:46:25
    power pivot求解目标: 1、各个区域的各个省份的销售金额。 2、各个区域的各个省份的订单数量。 3、各个区域的各个省份的大单数量。 4、各个区域的各个省份的大单比率。 5、各个区域的各个省份的省份任务额。 6、...

    源数据:

    power pivot求解目标:

    1、各个区域的各个省份的销售金额。
    2、各个区域的各个省份的订单数量。
    3、各个区域的各个省份的大单数量。
    4、各个区域的各个省份的大单比率。
    5、各个区域的各个省份的省份任务额。
    6、各个区域的各个省份的任务额完成比率。

    我的方法:
    求解,各个区域的各个省份的,需要自行车订单明细与省份区域关联。
    1、销售金额,自行车订单明细.金额,求和。
    2、订单数量,自行车订单明细.订单编号,计数。
    3、大单数量,判断是否大单,自行车订单明细.金额,if函数,再对大单计数。
    4、大单比率=大单数量/订单数量
    5、省份任务额,省份任务金额与自行车订单明细关联。(错误)
    6、任务额完成比例=省份任务额/销售金额。

    我的操作:

    题5:正确做法,省份任务额,省份任务金额与省份区域关联。省份区域(区域、省份)同时控制省份任务金额(省份任务额)、自行车订单明细(销售金额),从而筛选“任务额完成比例=省份任务额/销售金额”。

    是否大单:=if([金额]>50000,1,0)
    销售金额:=SUM([金额])
    订单数量:=COUNTA([订单编号])
    大单数量:=SUM([是否大单])
    大单比率:=DIVIDE([大单数量],[订单数量])

    省份任务额:=SUM([任务额])
    任务额完成率:=DIVIDE([销售金额],[省份任务额])

    展开全文
  • power pivot聚合函数

    千次阅读 2018-10-29 16:14:02
    power pivot实现以下需求: 1、各个省份的订单金额分别是多少? 2、各个省份的订单数量分别是多少? 3、各个省份的平均订单金额分别是多少? 4、各个省份的客户数量分别是多少? 5、各个省份的最大订单的金额分别...

    用power pivot实现以下需求:
    1、各个省份的订单金额分别是多少?
    2、各个省份的订单数量分别是多少?
    3、各个省份的平均订单金额分别是多少?
    4、各个省份的客户数量分别是多少?
    5、各个省份的最大订单的金额分别是多少?
    6、各个省份的最小订单的金额分别是多少?

    我的方法:
    先在power pivot中建立度量值,再用数据透视表分析。以上需求都是使用“客户省份”这个维度分析。
    题1:度量值,求和,sum。
    题2:度量值,计数。
    题3:度量值,平均数。
    题4:度量值,非重复计数。
    题5:度量值,最大值。
    题6:度量值,最小值。

    我的操作:

    订单金额:=SUM([金额])
    平均金额:=AVERAGE([金额])
    最大订单金额:=MAX([金额])
    最小订单金额:=MIN([金额])
    订单数量:=COUNTA([订单编号])
    客户数量:=DISTINCTCOUNT([客户ID])

    作者:数据分析秦翼臣
    来源:CSDN
    原文:https://blog.csdn.net/qq_35990702/article/details/82354473
    版权声明:本文为博主原创文章,转载请附上博文链接!

    展开全文
  • 如何使用 Power Pivot 进行模糊匹配

    千次阅读 2020-03-31 21:07:51
    之前在《使用Power Query进行模糊匹配》一文中我们讨论了如何在Power Query中进行模糊匹配,今天我们来讨论下在Power Pivot中的模糊匹配。 还是之前的案例,图1为产品表,图2为颜色价格匹配表,需要根据颜色将颜色...
  • Power BI(十五)power pivot之Summarize函数 我们想统计订单金额的销售金额区间分布,想得到的效果图如下 如下图一笔订单会有多笔记录 传统的方法 生成数据透视表 复制到一个新表粘贴为值 添加...
  • Power BI(十二)Power Pivot时间智能函数 常用时间计算指标: 缩写 英文 解释 YTD Year To Date 年初至今的累计 QTD Quarter ...
  • Power BI(十一)Power Pivot常用DAX函数

    千次阅读 2019-03-31 23:30:41
    Power BI(十一)Power Pivot常用DAX函数 主要提及以下的函数,用法其实和平常的Excel公式差不多 数据的提取和合并函数 数据的格式转换函数 聚合函数 逻辑函数 信息函数 数学函数 文本函数 日期和时间函数 ...
  • Power BI(十八)power pivot之高级聚合函数 power pivot常见的高级函数如下: 函数 说明 SUMX 求和 AVERAGEX 求平均值 MAXX 求...
  • Power Pivot 里面的参数只有两个(只能放两个参数) && 符号《&&》和and效果差不多,通常和if一起使用 =if(第一个条件&&第二个条件&&第三个条件&&…,“符合条件返回的...
  • 5.1 power pivot聚合函数

    千次阅读 2018-09-03 20:39:54
    power pivot实现以下需求: 1、各个省份的订单金额分别是多少? 2、各个省份的订单数量分别是多少? 3、各个省份的平均订单金额分别是多少? 4、各个省份的客户数量分别是多少? 5、各个省份的最大订单的金额分别...
  • 是一门函数语言,用于SSAS表格模型,而Power PivotPower BI Desktop本质上运行的都是SSAS实例,只不过根据各自应用场景做了阉割。所以学会了DAX函数,可以方便的在Excel/Power BI/SSAS 三者之间切换,从自助BI到...
  • Power BI(十九)power pivot之常用筛选器函数 power pivot常用筛选器函数如下: 函数 说明 FILTER 按条件筛选数据 VALUES 返回列或者表去重后的结果 ...
  • power pivot关于Calculate删选函数的使用

    千次阅读 2019-01-21 12:42:05
    Calculate函数 使用说明:Calculate(’[列],【表】【列】外加条件1,【表】【列】外加条件1…【表】【列】外加条件N) 比如要删选出是其他的对应的金额是总计是多少的话就是表达式为: CALCULATE(sum([金额],...
  • powerpivot 使用PowerPivot钻取数据 (Drill Into Data With PowerPivot)Have you tried Microsoft PowerPivot for Excel 2010 (formerly Gemini)? It’s a powerful data analysis add-in for Excel, and is part of...
  • Power BI(十七)power pivot之使用Generate函数创建辅助表 Generate函数可以生成一张笛卡尔积表格 但是现实生活中两个表格可能都会存在重复值,我们该如何生成一张非重复的笛卡尔积表格呢? 表格一如下:...
  • 给大家分享一套课程——Excel Power Pivot 视频教程 数据建模及DAX函数,完整版视频教程下载。 零基础到高级应用系统化讲解Power Pivot课程,涉及常规透视表与数据模型透视表的区别,导入数据等基础应用,以及更多DAX...
  • Power BI(二十)power pivot之Earlier函数 - DAX进阶的里程碑 Earlier函数之累计求和、累计百分比 帕累托法则往往称为二八原理,即百分之八十的问题是百分之二十的原因所造成的。在帕累托图中,不同类别的数据...
  • Power Pivot关系函数

    2021-07-03 11:49:13
    related 将一端的内容拿到多段 一个参数 related(列名称) relatedtable 将多段的数据拿到一端 一个参数 relatedtable(表名) calculate 筛选度量值(类似条件求和) 有两个参数 calculate(筛选范围【度量值】,筛选...
  • 零基础到高级应用系统化讲解Power Pivot课程,涉及常规透视表与数据模型透视表的区别,导入数据等基础应用,以及更多DAX基础语法及拓展高级应用,设置度量值,理解查询上下文,筛选上下文,行上下文之间的关系。
  • Power Pivot DAX函数

    2021-07-03 15:26:54
    (1)类似于excel函数 (2)智能感知 (a) 单引号 表以及表的字段 (b)中括号 度量值 (3) if函数 和excel一样 (4) and (a) 在pp里面的参数只有两个 (b)&& 和if一起使用 =if(第一个条件&&...
  • power pivot&power view

    千次阅读 2018-10-29 21:00:58
    解决方法:在powerpivot操作界面,增加辅助列(提取月的函数month(日期)) 注意:1月-12月为什么不能排序–文本不能排序 将自行车,服装,辅助产品按次排序 1).在powerpivot操作界面,筛选显示自行车,后边的添加列...
  • power pivot DAX函数

    2021-07-03 15:33:30
    1.关系函数 (1)related 将一端的内容拿到多端 参数:列名称 案例:大单比例 将区域拿到销售表 大单的最低金额拿到销售表 判断这个订单是否是大单 销售表中本身实际销售金额和大单的最低金额做对比 求出大单的数量 ...
  • 焦棚子的文章目录 ...说明 1、基于DAX Studio 2.9.2版本导出整理; 2、DAX Studio网站,及时...4、这里只是把DAX函数的说明拿出来,具体示例参见【DAX函数官方说明】。 ---------------我是分割线,以下即DAX函数说明,按
  • Excel数据分析工具:PowerPivot

    千次阅读 2020-05-06 18:27:49
    PowerPivot工具中,无须使用类似VLOOKUP函数进行字段匹配,只需要像Access数据库那样建立两表之间的关联关系,即可把两表根据关键字段关联起来。 此时,我们需要将“订购明细”表与“用户明细”表根据关键字段...
  • Power Pivot

    2020-08-09 20:53:51
    Power Pivot 一个案例了解Power Pivot 订单明细表 区域 产品表 关键字段 创建关系 一对多: 一指的是创建关系字段是唯一值 多指的是创建关系字段的重复值 多对一 一对一 一端的字段可以...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,780
精华内容 712
关键字:

powerpivot函数

友情链接: Visual.C1.zip