Aggregating daily data.

[jazking123]

I am trying to aggregate my daily frequency data into weeks and then calculate an average of weekly sales.

So the average-average for the following data would be

Week	Sales
1	50
1	65
2	52
2	63
3	12
3	16

Week 1: 50+65 = 115

Week 2: 52 + 63 = 115

Week 3: 12 + 16 = 28

Average Sales by week = 86.

It's important that I calculate it this way as I'd like to use the VAR.S command to calculate the variance of Weekly Sales.

Thanks for any assistance,

Regards,

Gareth.

[Phil_Seamark · Accepted solution]

Hi @jazking123

HEre is one way (of a few) that you can do it

Sales2 = IF(
				ISFILTERED('Table1'[Week]), 
				CALCULATE(SUM('Table1'[Sales])),
				DIVIDE
					(
						SUM(Table1[Sales]),
						DISTINCTCOUNT('Table1'[Week]))
					)

[jazking123]

That's perfect.

Thank you sir.

*Update*

Sadly I think i was mistaken, the reply posted seems to calculate the average value. I need the process to occur in two stages. I'm hoping that the first aggregates by week and the second takes the average of the weeks. I need it to do this so I can calculate the variation between weeks.