Forum Discussion

jasontlambert's avatar
jasontlambert
Frequent Visitor
8 years ago

Average weekly sales

I have sales data in weekly buckets.  I need to calculate two measures using the sales data.  The first measure is to calculate each week's total sales and divide by the average weekly sales for that...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi jasontlambert,

 

You can use year and weeknum as parameter to filter your table, then calculate on the filtered records to get weekly average.

Sample measure:

Week AVG =
AVERAGEX (
    FILTER (
        ALL ( 'Table' ),
        YEAR ( [Date] ) = YEAR ( MAX ( [Date] ) )
            && WEEKNUM ( [Date], 1 ) = WEEKNUM ( MAX ( [Date] ), 1 )
    ),
    [Amount]
)

 

 

Regards,

Xiaoxin Sheng

jasontlambert's avatar
jasontlambert
Frequent Visitor
8 years ago

Anonymous, thank you, but that's not quite what I'm trying to get.  If it helps, I have added a more detailed screenshot of my data in excel, along with formulas from Excel that gives the results I am trying to get. 

 

I want to take each week's sales data and divide it by the average for the year.  My total sales for 2015 is 80,190.  If you divide this by 52 (the number of weeks), you get 1,572.35.  My week 1 sales are 1,014.  If you divide 1,014 by 1,572.35 you get 0.6401 as my desired output.  

 

I also have lots of products that I have sales for and will be switching between products in a slicer or filter.  The formula you provided was giving the same results for all of the products, no matter which one I filtered on.