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
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
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]))
)
)