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 year.  My sales data goes back to 2015.  In my image below, the first week of 2015 has 1,603 in sales.  The total sales in 2015 is 104,806 and there are 52 weeks of data.  So 1,603 / (104,806/52) = 0.795.  Week 2 total is 1,114, so the measure should calculate 0.5527.  When the values switch to 2016, the measure needs to begin factoring in the average for 2016.  One other issue is that for 2017, we only have 45 weeks of YTD sales, so the measure must calculate the average correctly with the 45 weeks instead of 52, and when I update the data next week it should change to 46, etc. 

 

The second measure that I need is to be able to calculate the average of Week 1 from each year, the average of week 2 for each year, etc.  In the first step, the output from week 1 of 2015 is 0.795.  There will be an output from week 1 of 2016 and an output from week 1 of 2017.  My final output will be the average of week 1 across all three years, the average of week 2 for all three years and so on.   

 

I already have the data in one table and a separate dates table with my company's fiscal year, etc.  

 

Can someone help me create these two measures? 

 

 

Need Help

7 Replies

  • 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.  

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        Share the link from where i can download your Excel file (the last one where you have shown an illustration).