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...

Need Help

v-juanli-msft

7 years ago

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]/3

Best Regards

maggie

Ave per week =
DIVIDE (
CALCULATE ( SUM ( Table1[NScheduled] ), ALLEXCEPT ( Table1, Table1[Week] ) ),
CALCULATE ( COUNT ( Table1[Week] ), ALLEXCEPT ( Table1, Table1[Week] ) )
)

moving sum = CALCULATE ( SUM ( 'Table2'[Ave per week] ), FILTER ( ALL ( 'Table2' ), [Week] < EARLIER ( 'Table2'[Week] ) && [Week] >= EARLIER ( 'Table2'[Week] ) - 3 ) )

MA3 = [moving sum]/3

Forum Discussion

Averageif