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

chethan's avatar
chethan
Resolver III
7 years ago

tomgag specify what is MA & Share the sample date with the clear brief explanation what your looking

 

 

Regards,

Chetan K

tomgag's avatar
tomgag
Resolver I
7 years ago

MA is moving average. is it will be in this case:

Week 4 MA = (Week 1 value 1 + Week 2 value 3.5 + Week 3 value 2.67 ) / 3

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    A moving average has this general format (from the Rolling Average Quick Measure)

     

    Value rolling average = 
IF(
	ISFILTERED('Table2'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __LAST_DATE = ENDOFMONTH('Table2'[Date].[Date])
	VAR __DATE_PERIOD =
		DATESBETWEEN(
			'Table2'[Date].[Date],
			STARTOFMONTH(DATEADD(__LAST_DATE, -3, MONTH)),
			ENDOFMONTH(DATEADD(__LAST_DATE, 1, MONTH))
		)
	RETURN
		AVERAGEX(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('Table2'),
					'Table2'[Date].[Year],
					'Table2'[Date].[QuarterNo],
					'Table2'[Date].[Quarter],
					'Table2'[Date].[MonthNo],
					'Table2'[Date].[Month]
				),
				__DATE_PERIOD
			),
			CALCULATE(SUM('Table2'[Value]), ALL('Table2'[Date].[Day]))
		)
)