Forum Discussion

jazking123's avatar
jazking123
Frequent Visitor
9 years ago
Solved

Aggregating daily data.

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.

 

 

 

 

 

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

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

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      jazking123
      Frequent Visitor
      9 years ago

      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.