This content has been marked as final.
Show 2 replies
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!