Forum Discussion
Solved
Averageif
Hi, I try to do 3 weeks MA and 'Ave per week' for the below data. Note 'Ave per week' is for info only. Week NScheduled Ave per week 1 1 1 1 1 1 1 1 1 1 1 1 2 6 3.5...
Hi tomgag
In your original table, create a calculated column
Ave per week =
DIVIDE (
CALCULATE ( SUM ( Table1[NScheduled] ), ALLEXCEPT ( Table1, Table1[Week] ) ),
CALCULATE ( COUNT ( Table1[Week] ), ALLEXCEPT ( Table1, Table1[Week] ) )
)Then create a new table from Table1
Table2 = SUMMARIZE(ALL(Table1),[Week],Table1[Ave per week])
In this table, create calculated columns
moving sum = CALCULATE ( SUM ( 'Table2'[Ave per week] ), FILTER ( ALL ( 'Table2' ), [Week] < EARLIER ( 'Table2'[Week] ) && [Week] >= EARLIER ( 'Table2'[Week] ) - 3 ) )
MA3 = [moving sum]/3Best Regards
maggie
Ave per week as a column would look like:
Ave per week =
VAR __table = FILTER(ALL('Table'),[Week]=EARLIER([Week]))
RETURN AVERAGEX(__table,[NScheduled])