PowerBI中DAX实现按公司分组计算门店租金平均值的问题

现有模型与已创建度量值 #

• 表结构：dim store、dim company、fct storeValues，其中dim store分别与dim company和fct storeValues建立关联，同时存在日期表等其他表
• 已实现计算单门店租金总和的度量值Rent：

CALCULATE(SUM('fct storeValues'[value]), 'fct storeValues'[type] = "rent")

需求说明 #

需要实现按公司+年度分组，计算该公司对应年度下所有门店的租金平均值，对应SQL逻辑如下（按年度聚合）：

SELECT 
    Year, CompanyName, StoreName, Rent, AVG(Rent) OVER (Partition by CompanyName)
FROM (
    SELECT
        t1.Year, t3.CompanyName, t2.StoreName, sum(t0.value) as Rent
    FROM    
        fctStoreData t0
        inner join DimDate t1 on t1.Datum = t0.Datum
        inner join DimStore t2 on t2.id = t0.standortId
        inner join DimCompany t3 on t3.id = t2.kundeId
        WHERE
                t0.type = 'rent'
    GROUP BY
        t1.Year, t3.CompanyName, t2.StoreName
) agg 

预期输出结果 #

现有可运行但冗余的DAX方案 #

avg Rent = 
var customer = SELECTEDVALUE('DIM Company'[ID])
var groupedRent = CALCULATE([Rent], 'DIM Company'[ID] = customer, REMOVEFILTERS('DIM Store'))
var numStores = CALCULATE(SUMX(VALUES('DIM Store'[Id]), 1), ALLEXCEPT('DIM Store', 'DIM Store'[CompanyId]), 'DIM Store'[CompanyId] = customer, ALLSELECTED('DIM Company'))

var numCustomers = CALCULATE(SUMX(VALUES('DIM Company'[ID]), 1))
var avgOverallRent = DIVIDE([Rent], CALCULATE(SUMX(VALUES('DIM Store'[Id]), 1)))

RETURN 

if ([Rent] && numCustomers = 1, DIVIDE(groupedRent, numStores), if([Rent] && numCustomers > 1, avgOverallRent, BLANK()))

优化后的简洁DAX方案 #

利用DAX筛选上下文函数直接控制计算范围，逻辑清晰且代码简洁：

AvgRent = 
CALCULATE(
    AVERAGEX(VALUES('dim store'[Id]), [Rent]),
    REMOVEFILTERS('dim store'),
    KEEPFILTERS('dim company'),
    KEEPFILTERS('DimDate'[Year])
)

方案说明 #

• REMOVEFILTERS('dim store')：移除当前行的门店筛选，扩展计算范围到该公司该年份下的所有门店
• KEEPFILTERS('dim company')：保留当前公司的筛选条件，确保只计算对应公司的门店
• KEEPFILTERS('DimDate'[Year])：保留当前年份的筛选条件，确保计算的是年度内的平均值
• AVERAGEX(VALUES('dim store'[Id]), [Rent])：遍历该公司该年份下的所有唯一门店，计算每个门店租金的平均值

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