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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Follow on LinkedIn
@ 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]))
		)
)

Follow on LinkedIn
@ 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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors