4 Replies Latest reply on Jul 7, 2017 3:42 PM by malou.puyod.0

# Weighted Average (error: cannot mix non-aggregate and aggregate)

Hi,

I am having an error when trying to create a calculated for Weighted Average.  The error is it cannot mix aggregate and non-aggregate. I need to get the Weighted Average per Postal Code, State and for GRAND Total.  For the interval column : AVG(Interval) while to get the Order Volume it's COUNTD(Order ID).  The Weighted Average formula I searched from another post is :

SUM([Frequency]*[Sample Size])/SUM([Sample Size])   so in my scenario I'm trying this SUM (  AVG(Interval) * COUNTD(OrderID)  )   / COUNTD(OrderID) but the error occurred.  Can you please help me how to fix this?

See attached workbook.

Lou

• ###### 1. Re: Weighted Average (error: cannot mix non-aggregate and aggregate)

Hi,

Thanks

Deepak • ###### 2. Re: Weighted Average (error: cannot mix non-aggregate and aggregate)

SUM({FIXED [State],[Postal Code],[Manufacturer (copy)],[Order ID]:AVG([Sales])})*(COUNTD([Order ID]))/(COUNTD([Order ID]))

• ###### 3. Re: Weighted Average (error: cannot mix non-aggregate and aggregate)

tHere is another Calculation which works. Modify as per your need.

SUM({FIXED [State],[Postal Code],[Manufacturer (copy)],[Order ID]:AVG([Sales])}*{FIXED:COUNTD([Order ID])})/MAX({FIXED:COUNTD([Order ID])})

1 of 1 people found this helpful
• ###### 4. Re: Weighted Average (error: cannot mix non-aggregate and aggregate)

Thanks Deepak!