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