Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Performance problems with moving average (over a index)

Hello, I have a problem with the calculation time when determining a moving average( not via date values, but via an index). The table is loaded into Power BI.
The table has about 100,000 rows and about 80 columns.
I calculate a moving average (calculated column) using:

 
Moving Average =
VAR MyIndex = RRK_A[Index]
VAR Window_A = 299
VAR myResult =
SUMX(
FILTER(
RRK_A,
RRK_A[Index] > MyIndex-Window_A &&
RRK_A[Index] <= MyIndex
),RRK_A[RRK_A_FLAG]
)
RETURN FIXED(myResult,2)
 

Further I use a MEASUR:

Moving1 =
VAR currentIndex = MAX('RRK A'[Index])
VAR Grenze = 10
VAR movingAverage = CALCULATE(SUM('RRK A'[RRK_A_flag]), FILTER(ALLSELECTED('RRK A'), 'RRK A'[Index] > currentIndex -
Grenze && 'RRK A'[Index] <= currentIndex))
return movingAverage

 


Does anyone have an idea how I can improve the performance?
Thanks for help

Need Help
Tips and Tricks

3 Replies