2 Replies Latest reply on Dec 5, 2017 1:29 PM by Chris McClellan

# Weighted Hours

Hello,

Every time I've posted a question on here you guys are so awesome - so thank you! I have a requirement for a complicated formula that I can't seem to figure out. The idea is a weighted average number of hours per invoice (weighted by the number of orders). So an invoice with more orders will be more weighted.

The formula my executive is requesting is:

(HOURS/INVOICE * ORDERS/INVOICE) / TOTAL ORDERS

One of the problems I am having is I derive the number of orders by COUNTD(Order Number) - so I can't use the 'SUM' function on a COUNTD.

Another problem is aggregation. The closest I got was

{ FIXED [Invoice Number] : SUM([Hours Difference])} * { FIXED [Order Number] : [Orders]}

-but I don't know how to take this number and divide by TOTAL ORDERS

I've attached a sample workbook. Any help is greatly appreciated. Thank you!

• ###### 1. Re: Weighted Hours

HI Galen,

I am struggling to understand your request.

Could you pick one real number as example and explain your expected value?

Regards,

Shin

• ###### 2. Re: Weighted Hours

WOW v.9.3, I'm hoping this still works in 9.3 ....

Here's the result ...

To get TOTAL ORDERS I did ..

Then I used the formula that you provided