2 Replies Latest reply on Dec 21, 2015 7:37 AM by Rody Zakovich

    Monthly avg sales issue

    Jessy Cool

      How to get Monthly Avg sales  for each category .

       

      i use avg (sales ) but dont know how to get avg sales per month

        • 1. Re: Monthly avg sales issue
          Thomas McCullough

          Hi Jesse,


          The attached workbook shows a solution to get average monthly sales by Product Category using the WINDOW_AVG table calculation function.

           

          Cheers,

          Tom

          • 2. Re: Monthly avg sales issue
            Rody Zakovich

            You could also use an LoD.

             

            First step is to create a field that defines the Month and Year. Here are some options.

             

            1. Create Custom Date Field

             

             

            2. Create a calculated field that basically does the same thing

             

            (DATEPART('year', [Order Date])*100 + DATEPART('month', [Order Date]))

             

            3. Use Truncation

             

            DATETRUNC('month', [Order Date])

             

            Either of these will work, and they all give you a field to use in an LoD, i.e. Year and Month (2015-12)

             

            Once you have that, you can just reference it using an INCLUDE LoD

             

            AVG({ INCLUDE [Order Date (Month / Year)] : SUM([Sales]) })

             

            Since you are using an LoD, you don't need to worry about adding a LAST() function, or adding the Order Date to the Detail shelf.  And you can filter, without issues.

             

             

            Regards,

            Rody