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

    Weighted Hours

    Galen Flynn

      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!