Hi BI Developers,
I have created a BEX query using Query Designer. In the query I am using formulas to calculate weighted forecast accuracy at the Location (Plant)/Product level. When I execute the query, the formulas work fine in the rows and even in the subtotal for products in one plant.
The problem is in the Overall Results row (the yellow row at the very bottom). The Overall Result for a particular key figure is calculated differently at the Grand Total level than the subtotal level.
I've tried using the boolean LEAF operator but it doesn't give me the desired outcome. It allows me to define a different calculation for ALL results rows (subtotals and totals). What I need is a way to have the same calc for individual rows and sub total rows, but a different calc for grand total rows.
Is there a way to do achieve this outcome? VBA? Particular formula?
Thank you!