8 Replies Latest reply on Oct 12, 2018 2:38 AM by Alex Martino

    Monthly averages not making sense

    Alex Martino

      Hello. I have products summed by monthly sales total. When I use average the numbers don't make any sense. Not sure why. Please look at "Sales by Product, by Month Average" sheet attached. Thanks.

        • 1. Re: Monthly averages not making sense
          Alex Martino

          Any thoughts on this? thanks!!

          • 2. Re: Monthly averages not making sense
            lei.chen.0

            Hello Alex,

             

            Please specify the reason why you think it doesn't make sense.

             

            This screenshot is just showing that, average = sum / row numbers .

             

            Regards

            Lei

            • 3. Re: Monthly averages not making sense
              Alex Martino

              Hello. Because there are only 30 days in each of those months. I want the daily average.

               

              Thanks

              • 4. Re: Monthly averages not making sense
                lei.chen.0

                Hello Alex,

                 

                Got it. Precise description is important.

                 

                Alex Martino wrote:

                 

                I want the daily average.

                 

                Please try,

                 

                AVG({ FIXED [Category], [Date]: SUM([$ of Sales])})

                 

                 

                Regards

                Lei

                1 of 1 people found this helpful
                • 5. Re: Monthly averages not making sense
                  Alex Martino

                  Thanks Lei. That worked. Can you explain what the fixed date part is doing? Is that fixing it at the month level? How does it know what level my data dimension is at (daily, monthly, etc)?

                  • 6. Re: Monthly averages not making sense
                    Alex Martino

                    Lei Chen

                     

                    I have two follow up questions:

                     

                    1) Using women's slouches as an example (last product) it says September average was .8, but I would like the average to start only for the first positive value during the entire data set. That item was a new item and thus didn't have any sales on all the days prior to the first day where it was above 0. Do you know how to modify your formula to account for that?

                     

                    2) If I change the data to annual instead of monthly for the average will I need to change anything? And how can I implement the same rule from above AND have the yearly average ignore months where there was no data (because looking at slouchies, the annual number doesn't look correct in year sheet).

                     

                    Thanks!!

                    • 7. Re: Monthly averages not making sense
                      lei.chen.0

                      Hello Alex,

                       

                      Alex Martino wrote:

                       

                      Is that fixing it at the month level? How does it know what level my data dimension is at (daily, monthly, etc)?

                       

                      FIXED works at per day (continuous) per category level.

                       

                      Alex Martino wrote:

                       

                      2) If I change the data to annual instead of monthly for the average will I need to change anything? And how can I implement the same rule from above AND have the yearly average ignore months where there was no data (because looking at slouchies, the annual number doesn't look correct in year sheet).

                       

                      The same question, why it looks incorrect to you?

                      34 / 124 days = 0.27

                       

                      Alex Martino wrote:

                       

                      1) Using women's slouches as an example (last product) it says September average was .8, but I would like the average to start only for the first positive value during the entire data set. That item was a new item and thus didn't have any sales on all the days prior to the first day where it was above 0. Do you know how to modify your formula to account for that?

                       

                      How to judge it's a new item or not?

                       

                      For positive values, try something like, IIF([Value]>0, [Value], 0)

                      To eliminate zero-value day from denominator, try SUM(...) / COUNTD(IF [Value]>0 THEN [Date] END) instead of AVG

                       

                       

                      Regards

                      Lei

                      1 of 1 people found this helpful
                      • 8. Re: Monthly averages not making sense
                        Alex Martino

                        Hi Lei,

                         

                        Understood on Fixed date. Thanks. If I change it to month(date) what does that do?

                         

                        2) Because the product has not existed for 124 days. It has only existed since the 17 of September. So the daily average for September should START at the point where there is data. Also, the other months that are 0 should not count in the average. It is similar to question 1. So they yearly daily average is different for products that are now since it is not counting every day in the period.

                         

                        1) The way to judge if it's new is to look at all the data in the period (in this case June through October) and find the point (or date) where there is the first positive value and then JUST FOR THAT MONTH calculate the difference between the last data in the month and the first date with data and use that as the denominator for daily average in that month.

                         

                         

                        I didn't quite understand how to implement your formulas. Thank you for your help.