Bad DAX code? Weighted average calculation works, but is SLOW

[Anonymous]

I have 30,000 stores, and a table that records their status each day - including how many days have passed since each store has been serviced.

I've created a measure that tells me what the average number of days is since store service for the network:

Avg Days Since Service :=
    divide(
        sumx('Store Status Fact',
        'Store Status Fact'[DaysSinceService] * [Store Count]),
    [Store Count]
)

This yields the correct answer, but is too slow to be usable - takes 1-2 minutes to get back to me.

Goal is to make a simple slice-able line time series chart tracking daily average days since store service.

What's a faster reformulation of this calculation?

[v-yulgu-msft]

Hi @Anonymous ,

Sometimes, SUMX might decrease the execution performance. You could replace SUMX with SUM instead.

When to Use SUM() vs SUMX()

Tune your PowerPivot DAX query, don’t use the entire table in a FILTER and replace SUMX if possible

Besides, you could run DAX Studion to track performance.

Best regards,

Yuliana Gu