Reading the Higher Level figures

[PrevContributor]

Dear Team, I have a business scenario (refer to attached file). The Formula for Net Volume is not working because Returns % are maintained by Year however even if the Returns % are maintained by Months it is not picking up the values In addition to above, YEARVALUE does not allow selection using SELECT or LOOKUP as parameter. For Example: YEARVALUE (Returns (LOOKUP: REGION) OR YEARVALUE ( Returns) [LOOKUP: REGION) gives error messages Given above I could ask Users to maintain the returns % by month,  add a formula as following to read return % IF Customers.Territory = Territory.US THEN Returns[SELECT: Territory.US] ELSE 0 Question is there is a better  way to achieve above? Thanks for reading my post (refer attachment) and look forward for your suggestions! Cheers, Umesh

[PrevContributor · Accepted solution]

Hi Umesh - I'd be inclined to just have a separate line item named something like 'Returns % by month' which is split by Territory and Month.  This would just have the formula YEARVALUE(Returns). Your net volume formula would then just be Net Volume = Gross Volume * (1 - Returns % by Month[LOOKUP: REGION]) A nice extension on this might be to allow the users to override annual returns value in any individual month, i.e. have three line items in the Territory x Month module... 'Returns % by month' = YEARVALUE(Returns) Returns Override which is data input Finalised Returns = IF Returns Override <> 0 THEN Returns Override ELSE 'Returns % by month' Hope this helps.