3 Replies Latest reply on Aug 5, 2016 8:04 AM by Freddy W

    Average Size of Order

    Freddy W

      Hello,

       

      How can I modify this calculation so that any distinct order below $3,000 is excluded?

       

      SUM([Gross Sales])/COUNTD([Order ID])

       

      Thank you

        • 1. Re: Average Size of Order
          Chris McClellan

          Hmmm .. without doing any testing, I'd start with something like this :

           

          IF [Gross Sales] > 3000 then SUM([Gross Sales])/COUNTD([Order ID]) end

           

          or depending on what else you're doing with the calculated field, maybe this is better:

           

          IF [Gross Sales] > 3000 then SUM([Gross Sales])/COUNTD([Order ID]) ELSE 0 END

           

          if that doesn't work, can you mockup something with some sample data ?

           

          • 2. Re: Average Size of Order
            Jeff James

            Hi Freddy,

             

            Need some additional information to answer this.

             

            Are you looking to exclude the order from the numerator, denominator, or both?

            How is the data structured? I'm guessing, but is it line item detail, so that you need to sum up multiple rows to decide if the total for the order is < $3000?

             

            It generally helps if you can attach a workbook with sample data in it. The data structure is frequently the root cause of the problems with calculations.

            • 3. Re: Average Size of Order
              Freddy W

              I think I got it to work by placing "OrderID" under the filters shelf and adding the formula below under "Condition-by Formula"

               

              SUM([Gross Sales]) > 3000

               

              I could be wrong but will follow-up if I have any issues.

               

              Thank you!