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

    Weighted Hours

    Galen Flynn



      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:



      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!