Formula Logic Help
Got a question I need assistance with.
I have a number of lists that follow a hierarchical list, account level 4, 3, 2 ,1. At level 4 each account also belongs to an accounting group (online, local etc) and has a signage value of either 1 or -1.
The accounting group and signage are contained within a module called account lowest level mapping.
I am attempting to create a module consolidates my profit and loss by accounting group instead of the hierarchy that is set up.
The module uses the account group dimension and has a line item for amount. The current formula is 'Profit and Loss Module'.Month[SUM: Account Lowest Level Mapping.Account Group] which is incorrect as it does not include the signage.
If I use 'Profit and Loss Module'.Month[SUM: Account Lowest Level Mapping.Account Group] * lowest level mapping.signage this is incorrect as it needs to multiply each account level 4 item by the signage first, and then sum by account group.
Is this achievable through formulas alone? Or do I need another module to multiply the signage through first which I then sum by.
Any assistance would be appreciated.