Hi,
Need help with InputAvg feature, please. I need to calculate ASP (Average Selling Price). It is calculated as SUM(GROSS REVENUE)/SUM(Volume). I want to use InputAvg(budget_ASP) function, to do "what-if" analysis by typing "what-if" ASPs into this field. It works fine when I stay at the lowest level of granularity but as soon as I start rolling up numbers, my total is just a simple average but I need a weighted avg by volume. See attached two tables.
My work-around solution is to have two tables: one with the ASP as input field and Gross Revenue calculated (because inputsum() works fine, right) and the second, Gross Revenue as input field and ASP calculated ([Gross Revenue]/[Volume]). I go to the lowest granularity and make change to ASP. After that, I take GR and type it into second table. In this case, my second table has correct ASP at the total/aggregated level.
Is there a way to calculated weighted average for inputavg() function or some other workaround? Otherwise, it is too manual. I want to "what-if" with not only ASP but AUC, AUP and so on.
Note: The second table has a desired ASP total, which is 113.22
| Input ASP | | | | |
| Capacity | 640 | 880 | 1000 | Total |
| Net Volume | 16,000 | 4,000 | 4,000 | 24,000 |
| Gross Revenue | $1,515,200 | $560,000 | $642,000 | $2,717,200 |
| ASP | $94.70 | $140.00 | $160.50 | $131.73 |
| | | | |
| | | | |
| Input GR | | | | |
| Capacity | 640 | 880 | 1000 | Total |
| Net Volume | 16,000 | 4,000 | 4,000 | 24,000 |
| Gross Revenue | $1,515,200 | $560,000 | $642,000 | $2,717,200 |
| ASP | $94.70 | $140.00 | $160.50 | $113.22 |
