Hey everyone,
I've got two questions regarding weighted averages. Please see the attached document to better understand what I'm trying to accomplish.
I have two tables - Invoices and Goals. Goals are a set number of items that someone must sell. The invoices are summed up by units to see how close to each goal we can be. Each goal has a maximum percentage of 110%. Each supplier's "% to Goal" should be the weighted average of the "% to Goal" for each of the products that they offer. Products with no goal record (sum of goals = 0) should be excluded from the calculation but shown in the table.
In my attached document supplier 001 has two products, with completion percentages of 50% and 110%. The average of these two numbers is 80%, which is correct.
Problem #1 - In the "Goal Detail" table the subtotal lines only calculate using the expression. This means for supplier 001 we are showing a % to Goal of 63.6%. This should be 80% which is the average of the two goals (110 + 50 / 2). How can I fix this?
Problem #2 - In the "Goal Summary" grid I have the same problem with % to Goal calculation. How can I get this column to display an average of the percentages for each goal that supplier has? I've tried using the Aggr() function in many forms with no luck.
The attached document is a simplified example of what I'm trying to accomplish. In my real document I'll be building on this, as well as using some set analysis. I've also included two different "% to Goal" columns - each is just a different way to do the calculation as it is now. One references other expressions and one calculates it's own sums.
Please let me know if you need any more information.
Any help would be appreciated!
Thank you.
