Row Average Calculation

[Alteryxexpert]

I have below Data

Item	Date	Sales
Chair	01/01/2024	100
Chair	02/01/2024	200
Chair	03/01/2024	300
Chair	04/01/2024	200
Chair	05/01/2024	200
Chair	06/01/2024	200

I need to calculate daily sales average like output below,.

Item	Date	Sales	Sales Avg
Chair	01/01/2024	100	100
Chair	02/01/2024	200	150
Chair	03/01/2024	300	200
Chair	04/01/2024	200	200
Chair	05/01/2024	200	200
Chair	06/01/2024	200	200

I'm wondering which macro does this for me, can some one help?

[Hsandness]

Hi,

You could use a RecordID and Multi-Row Formula tool to achieve this.

[davidskaife]

Hi @Alteryxexpert 

You could use the Multi-Row Formula tools to create a running average:

First tool creates a running total (grouped by Item), second tool creates a Record ID (again grouped by Item), Formula tool then divides the running total by the record id. Finally remove any fields not required.

[Alteryxexpert]

I want this calculation to start fresh on every new month, will the same work?

[flying008]

Hi, @Alteryxexpert 

FYI.

Item	Date	Sales	Sales Avg
Chair	01/01/2024	100	100
Chair	02/01/2024	200	150
Chair	03/01/2024	300	200
Chair	04/02/2024	200	200
Chair	05/02/2024	200	200
Chair	06/02/2024	200	200

[Qiu]

@Alteryxexpert 
I found this post and it should help.
https://www.thedataschool.co.uk/jeremy-kneebone/calculating-moving-average-alteryx/

[davidskaife]

Hi @Alteryxexpert 

Not as it stands, i've attached a revised workflow and added in a Formula tool to generate Month/Year column, and added that is as a Group By criteria for the Multi-Row Formula tools.