Power BI中计算船舶Day Diff的均值、最大值与最小值问题

背景信息 #

• 数据库：BDD，核心列：
  • Program：项目日期（船舶服务日期的定义时间）
  • Date：BT处理日期（不同项目间可变化）
  • Ship：船舶名称
  • Owner of the shipment：货主
• 创建了Measures表，定义3个度量值：
  1. First program：查找每艘船舶首个项目对应的服务日期（Date列）
  2. Last program：查找每艘船舶最后一个项目对应的服务日期
  3. Day diff：通过DATEDIFF计算前两个度量值的天数差

问题 #

已筛选出货主为“我司”的矩阵，但无法计算Day diff的均值、总和、最大值/最小值：

• 因Day diff是度量值而非列，无法直接使用AVERAGE函数
• 尝试AVERAGEX、GEOMEANX、MAXX等函数均无效
• 当前计算结果异常：
  • GEOMEANX返回51，预期4.19
  • SUMX返回51，预期88

现有度量值代码 #

Last program #

CALCULATE(
    MAX(BDD[Date]), 
        ALLEXCEPT(BDD,BDD[Ship]),
            BDD[Program] = MAX(BDD[Program]))

First program #

CALCULATE(
    MIN(BDD[Date]), 
        ALLEXCEPT(BDD,BDD[Ship]),
            BDD[Program] = MIN(BDD[Program]))

Day Dif #

DATEDIFF([First program], [Last program], DAY)

尝试的计算代码 #

Mean day diff = calculate(GEOMEANX(Medidas,Medidas[Day dif]),FILTER(BDD,BDD[Owner]="Company"))

解决方案 #

问题根源 #

你用Medidas（即Measures表）作为X函数的迭代对象，但度量值表仅存储度量值定义，无实际数据行，迭代时只会计算一次全局的Day diff，而非按每艘船迭代，导致结果异常。

修正方案 #

需按每艘符合条件的船舶迭代，而非迭代度量值表。

1. 计算Day diff的总和

Total Day diff = 
SUMX(
    // 获取筛选后（货主为我司）的唯一船舶列表
    SUMMARIZE(FILTER(BDD, BDD[Owner of the shipment] = "我司"), BDD[Ship]),
    // 对每艘船计算Day diff并累加
    [Day diff]
)

2. 计算Day diff的均值

Mean Day diff = 
AVERAGEX(
    SUMMARIZE(FILTER(BDD, BDD[Owner of the shipment] = "我司"), BDD[Ship]),
    [Day diff]
)

也可通过总和除以船舶数量实现：

Mean Day diff = 
DIVIDE(
    [Total Day diff],
    COUNTROWS(SUMMARIZE(FILTER(BDD, BDD[Owner of the shipment] = "我司"), BDD[Ship]))
)

3. 计算最大值/最小值

Max Day diff = 
MAXX(
    SUMMARIZE(FILTER(BDD, BDD[Owner of the shipment] = "我司"), BDD[Ship]),
    [Day diff]
)

Min Day diff = 
MINX(
    SUMMARIZE(FILTER(BDD, BDD[Owner of the shipment] = "我司"), BDD[Ship]),
    [Day diff]
)

额外优化：简化First/Last Program度量值 #

原度量值逻辑可简化，无需嵌套聚合函数，直接按Ship分组取对应Program的Date：

First program = 
VAR MinProgramPerShip = CALCULATE(MIN(BDD[Program]), ALLEXCEPT(BDD, BDD[Ship]))
RETURN CALCULATE(MIN(BDD[Date]), BDD[Program] = MinProgramPerShip)

Last program = 
VAR MaxProgramPerShip = CALCULATE(MAX(BDD[Program]), ALLEXCEPT(BDD, BDD[Ship]))
RETURN CALCULATE(MAX(BDD[Date]), BDD[Program] = MaxProgramPerShip)

内容的提问来源于stack exchange，提问作者jvm.97