2 Replies Latest reply on Oct 12, 2018 10:42 PM by Alex Martino

    Monthly average calculation

    Alex Martino

      Hello. I have products that have transactional data (multiple orders for each day). I show the average sales for each product over 8 months and those numbers are fine. However, when I add a total average to the right, the products that are newer, and have two months of data, their overall average shows as really low because it divides by all showing months. How can I fix this?

        • 1. Re: Monthly average calculation
          meenu choudhary

          Hello Alex,

           

          Tableau takes all sales occurrences and creates an average for all of them. So automatic AVG aggregation in Tableau will do SUM (Sales) / COUNT (Sales). If we wish to calculate an average per month,we do need to make an extra calculated field since we want to have AVG aggregation as SUM (Sales) / COUNT (Months).

           

          You can try below logic:

           

          Monthly average sales = AVG({FIXED month([Order Date]),year([Order Date]):SUM([Sales])})

          • 2. Re: Monthly average calculation
            Alex Martino

            Hi meenu choudhary that doesn't seem to be it. That gives me the same numbers for all products.

             

            I added [product] to the fixed portion above. HOWEVER, this still doesn't fix the problem of the yearly daily average being incorrect for the products that are new.

             

            For example, look at the product Slouchie in the tab with your name on it. That product has information only for September and October. AND IN SEPTEMBER, it is a new product with only a small number of active days, so the average for that month is bad. I don't know how to get the correct yearly average by day (not including days before the release of the product). See attached spreadsheet.