资料文库

DAX高级函数详解！迭代器与高级上下文：让Excel数据自己「思考」

office

各位【Excel数据分析BI】的朋友们，是不是总觉得透视表和小函数解决不了复杂的业务问题？比如老板突然要你算「2025年单笔超过1万的大单总金额」，或者「每个销售在所属大区内的业绩排名」？别头疼了，今天带你认识Power Pivot里真正的「王牌」——迭代器函数与高级上下文。掌握了它们，你的Excel就不再是计算器，而是能理解业务逻辑的分析大脑！

这篇文章能帮你： 彻底告别「先加辅助列再透视」的笨办法，用几个公式直接搞定多层条件筛选、动态占比排名和复杂时间对比，实现数据分析的「一步到位」。

你将解锁：

  • FILTER： 你的「精细筛子」，专治各种复杂条件过滤

- SUMX/AVERAGEX： 内置「计算引擎」，行级运算告别辅助列

  • ALL函数家族： 「全局视角」开关，排名占比不再出错

- 时间智能函数： 「时间魔法师」，同比环比一键生成

  • VAR变量： 「公式定海神针」，复杂逻辑条理清晰

一、 FILTER函数：当简单筛选不够用时，请出你的「精细筛子」

想象一下，老板看着2025年的销售报表问：「那些单笔金额超过1万的大客户订单，总共有多少销售额？」你发现，普通的筛选和CALCULATE的简单条件写不了[销售额]>10000这种针对度量值结果的判断。这时候，FILTER就该上场了。

DAX公式实战：

// 计算2025年大单（>1万）总金额
大单总额 := CALCULATE([总销售额], FILTER('订单表', '订单表'[销售额] > 10000 && '订单表'[年份] = 2025))

// 计算销售员「张三」在2025年第三季度的大单数量
张三大单数 := COUNTROWS(FILTER('订单表', '订单表'[销售员] = "张三" && '订单表'[销售额] > 10000 && '订单表'[日期] >= DATE(2025,7,1) && '订单表'[日期] <= DATE(2025,9,30)))

公式原理解析：

FILTER是个「表函数」，它不直接计算，而是像筛子一样，返回一张过滤后的新表。FILTER(‘订单表’, …)会逐行扫描订单表，把符合条件的行筛出来。CALCULATE再对这个「筛后」的新表进行计算。FILTER之所以能逐行判断，是因为它是一个「迭代器」，它在工作时能意识到当前正在处理哪一行数据，这个意识就是「行上下文」。

一句话点醒你： 当你的筛选条件复杂到需要「且」、「或」逻辑，或者需要判断计算后的结果时，FILTER就是你的不二之选。

二、 SUMX/AVERAGEX：告别辅助列，你的「行级计算引擎」

算总利润，你是不是还在原始表里先加一列「利润=销售额-成本」，然后再求和？太麻烦了！DAX的迭代器函数（名字里带X的那些）能直接在公式里完成行级计算，一步到位。

DAX公式实战：

// 计算总毛利润（无需任何辅助列）
总毛利润 := SUMX('订单表', '订单表'[销售额] - '订单表'[成本])

// 计算平均订单金额（按订单号去重后的平均值）
平均订单金额 := AVERAGEX(VALUES('订单表'[订单号]), [总销售额])

// 计算每个产品的总利润（跨表引用成本价）
产品总利润 := SUMX(RELATEDTABLE('销售明细'), '销售明细'[数量] * (RELATED('产品表'[单价]) - '销售明细'[单位成本]))

公式原理解析：

SUMX(‘订单表’, ‘订单表’[销售额] - ‘订单表’[成本])这个公式干了三件事：1. 遍历「订单表」每一行（创造行上下文）；2. 对每一行计算销售额-成本；3. 把所有行的计算结果加起来。AVERAGEX(VALUES(…), …)则是先获取不重复的订单列表，再对每个订单计算其销售额，最后求平均。它们把「逐行计算」和「最终聚合」合二为一了。

一句话点醒你： 只要计算逻辑是「先对每一行做点什么，再把所有行的结果合起来」，就用带X的迭代器，它就是你公式里的「流水线」。

三、 ALL函数家族：想算排名和占比？你得学会「忘记」筛选

好不容易用RANKX做了个销售排名，拖到透视表里一看，每个人都是第一名！这是因为透视表的行标签「销售员」同时筛选了排名公式，每个人都在和自己比。要看到真正的全局排名，你需要ALL函数来「清除」筛选。

DAX公式实战：

// 正确的公司内部销售排名
销售排名 := RANKX(ALL('销售员表'), [总销售额])

// 计算每个人销售额占公司总销售额的比例
占比_全公司 := DIVIDE([总销售额], CALCULATE([总销售额], ALL('销售表')))

// 计算每个人在其所属大区内的销售额
占比占比_本大区 := DIVIDE([总销售额], CALCULATE([总销售额], ALL('销售员表'[销售员])))

公式原理解析：

ALL(‘销售员表’)的作用是移除对「销售员表」的所有筛选，让RANKX基于所有销售员的业绩进行排名。计算占比时，分母里的ALL(‘销售表’)移除了所有筛选，得到绝对的总和；而ALL(‘销售员表’[销售员])只移除「销售员」这一列的筛选，但保留「大区」等其他筛选，所以算的是在当前大区内的个人占比。还有一个ALLSELECTED函数，它只移除当前图表内的筛选，但保留页面切片器的筛选，适合做「在已选范围内的占比」。

一句话点醒你： ALL是帮你跳出局部、看清整体的「望远镜」，计算占比和排名时，想清楚你要的「总体」到底是什么范围。

四、 时间智能函数：让时间对比像呼吸一样自然

每月、每季度都要手动算「跟上个月比怎么样？」「今年累计到多少了？」，改来改去，烦不胜烦。如果你的模型里有一张标准的、连续的日期表，时间智能函数就是你的「时间魔法棒」。

DAX公式实战：

// 计算上月同期销售额
上月销售额 := CALCULATE([总销售额], PREVIOUSMONTH('日期表'[日期]))

// 计算2025年累计至今销售额
YTD销售额_2025 := TOTALYTD([总销售额], '日期表'[日期], "2025-12-31")

// 计算同比去年同期的增长率
销售额同比 := DIVIDE([总销售额] - CALCULATE([总销售额], SAMEPERIODLASTYEAR('日期表'[日期])), CALCULATE([总销售额], SAMEPERIODLASTYEAR('日期表'[日期])))

公式原理解析：

这些函数之所以智能，是因为它们基于一个完整的日期表来理解时间关系。PREVIOUSMONTH能自动找到当前筛选月份的上个月；TOTALYTD会计算从年初到当前筛选日期的累计值；SAMEPERIODLASTYEAR能智能匹配去年同一时间段（如周、月、季）。它们本质上都是通过CALCULATE修改了日期筛选上下文。

一句话点醒你： 用好时间智能函数，就是把重复的时间计算逻辑「外包」给DAX，你只需要关心业务结论。

五、 VAR变量：给复杂公式装上「模块」，清晰又高效

公式越写越长，逻辑绕来绕去，自己看着都晕，更别说维护了。DAX中的VAR（变量）能让你把中间结果「存起来」，给公式分模块，大大提升可读性和性能。

DAX公式实战：

// 使用变量优雅地计算Contoso品牌产品的毛利率
Contoso毛利率 :=
VAR ContosoSales = // 1. 定义变量：筛选出Contoso的销售记录    
    FILTER ( Sales, RELATED ( 'Product'[Brand] ) = "Contoso" )
VAR ContosoMargin = // 2. 定义变量：计算Contoso产品的总毛利
    SUMX ( ContosoSales, Sales[Quantity] * ( Sales[Net Price] - Sales[Unit Cost] ) )
VAR ContoSalesAmount = // 3. 定义变量：计算Contoso产品的总销售额
    SUMX ( ContosoSales, Sales[Quantity] * Sales[Net Price] )
VAR Result = // 4. 定义变量：计算最终比率
    DIVIDE ( ContosoMargin, ContoSalesAmount )
RETURN
    Result // 5. 返回最终结果

公式原理解析：

VAR关键字允许你在公式内部命名并存储一个值或表。上面公式中，ContosoSales这个变量只被计算一次，然后在后续两个变量中被重复引用。这样做的好处超多：逻辑像讲故事一样清晰，分步陈述；性能更好，避免了重复执行FILTER和SUMX；调试方便，可以单独检查每个变量的结果对不对。

一句话点醒你： 当公式逻辑超过三步时，就请用VAR把它拆解开。这就像写文章先列提纲，能让你的思路和公式都清爽无比。

总结一下：

DAX的威力，在于它用「声明式」的语言（告诉电脑你要什么结果），替代了「过程式」的手动操作（一步步教电脑怎么做）。迭代器（FILTER, SUMX）是你的「手」，让你能精细地处理每一行数据；而上下文（由CALCULATE, ALL等函数操控）是你的「舞台灯光」，决定了你看到的是局部特写还是全局全景。

把这两者结合好，你就能让数据按照你的业务逻辑动态「跳舞」。下次面对复杂问题时，先问自己：我要在什么范围内算？（用ALL/CALCULATE设定上下文）我要对哪些数据算？（用FILTER精细筛选）每一行怎么算？（用SUMX等迭代器定义规则）。你会发现，很多难题都能迎刃而解。

金句收尾： 普通用户用函数处理数据，高手用上下文驾驭数据。真正的效率提升，来自于让工具理解你的意图，而不是你重复机械的操作。