Forum Discussion
Solved
Weekly running average
Hi, I am new to Power BI and this is my first report. I have a dataset as follows: Loan Number Submission Week Volume Loan1 1 1000 Loan2 1 2000 Loan3 1 2000 Loan4 2 3000 ...
I figured it out. In the RunningSum calculation, it should be ALL('submit') instead of ALL('submit'[Submission Week]). Then it worked. Thanks!
Hi AG_PBI - create a measure for the total weekly volume as below
Total Volume =
SUM('Table'[Volume])
Now create one more measure to calculates the average of all weekly total volumes up to the current submit week.
Weekly Running Avg Vol =
VAR CurrentWeek = MAX('submit'[Submission Week])
VAR RunningSum =
CALCULATE(
SUM('submit'[Volume]),
FILTER(
ALL('submit'[Submission Week]),
'submit'[Submission Week] <= CurrentWeek
)
)
VAR WeekCount =
COUNTROWS(
FILTER(
ALL('submit'[Submission Week]),
'submit'[Submission Week] <= CurrentWeek
)
)
RETURN
DIVIDE(RunningSum, WeekCount)
)
RETURN
DIVIDE(RunningSum, WeekCount)
shared the expected output in above snapshot.
Hope it works.