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.

 

WeekNScheduledAve per week
111
111
111
111
263.5
213.5
342.666666667
332.666666667
312.666666667
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

6 Replies

  • 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]))
		)
)
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Ave per week as a column would look like:

     

    Ave per week =
VAR __table = FILTER(ALL('Table'),[Week]=EARLIER([Week]))
RETURN AVERAGEX(__table,[NScheduled])
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    What does your actual base data look like?  Is there is a Date column in there?  If no, do you atleast have a year and Month column?  Along with the Date column (if it is there), will there be a week column as well?

     

    Share the actual data and show the expected result.