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