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

# Average Size of Order

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

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

Hi Freddy,

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

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!