Forum Discussion

tomgag's avatar
tomgag
Resolver I
7 years ago
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...
Need Help
  • v-juanli-msft's avatar
    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