PowerBI中创建DAX Measure计算Fair Share Index的技术问询

数据表格 #

Table 1 #

Table 2 #

需求说明 #

计算Fair Share Index，公式为：
Index = [(Table1当前行Dollar Sales / Table1同Product同Time的National销售额) / (Table2当前行Cat Dollar Sales / Table2同Category同Time的National销售额)] * 100

示例计算 #

以Table1中Sting、Region1、1 Week行为例：
Fair Share Index = [(100/500)/(1000/10000)] * 100 = (0.2/0.1)*100 = 200%

解决方案 #

1. 修正数据模型 #

删除原有的多对多关联，在Table1和Table2之间建立一对多关系，关联字段为Product、Geography、Time（三个字段组合关联，确保每行唯一匹配）。

2. 创建DAX计算列 #

在Table1中新建计算列Fair Share Index，输入以下DAX公式：

Fair Share Index = 
VAR CurrentProduct = Table1[Product]
VAR CurrentTime = Table1[Time]
VAR CurrentCategory = RELATED(Table2[Category])
VAR CurrentCatSales = RELATED(Table2[Cat Dollar Sales])
-- 获取当前产品同时间的全国销售额
VAR ProductNationalSales = CALCULATE(
    SUM(Table1[Dollar Sales]),
    ALL(Table1[Geography]),
    Table1[Product] = CurrentProduct,
    Table1[Time] = CurrentTime
)
-- 获取当前品类同时间的全国品类销售额
VAR CategoryNationalSales = CALCULATE(
    SUM(Table2[Cat Dollar Sales]),
    ALL(Table2[Geography]),
    Table2[Category] = CurrentCategory,
    Table2[Time] = CurrentTime
)
-- 计算两个比率，用DIVIDE避免除以0错误
VAR ProductRatio = DIVIDE(Table1[Dollar Sales], ProductNationalSales, 0)
VAR CategoryRatio = DIVIDE(CurrentCatSales, CategoryNationalSales, 0)
-- 最终计算指数
RETURN
DIVIDE(ProductRatio, CategoryRatio, 0) * 100

公式说明 #

• VAR：定义当前行的变量，简化重复引用
• RELATED：通过关联关系从Table2获取当前行对应的品类和品类销售额
• CALCULATE + ALL：移除Geography的筛选，获取同Product/同Category、同Time的全国总销售额
• DIVIDE：安全除法，避免除数为0导致的错误

内容的提问来源于stack exchange，提问作者Loki