Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
tomgag
Resolver I
Resolver I

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
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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

1.png

 

Best Regards

maggie

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-juanli-msft
Community Support
Community Support

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

1.png

 

Best Regards

maggie

Greg_Deckler
Super User
Super User

Ave per week as a column would look like:

 

Ave per week =
VAR __table = FILTER(ALL('Table'),[Week]=EARLIER([Week]))
RETURN AVERAGEX(__table,[NScheduled])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
chethan
Resolver III
Resolver III

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

 

 

Regards,

Chetan K

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.