Department as a % of Total Revenue

CamdenMaggos

Hi everyone,

 

I am currently trying to develop a model that references two different regions within a list (that is production data).

 

Let's say we have 2 line items:

 

New England Revenue

Total Company Revenue

 

I want a ratio where I can reference our P&L (300ish line items) and say P&L Revenue [Select: List.New England] / P&L Revenue [Select: List.Total Company] but with it being production data I am unable to reference it. 

 

Is there a work around for this?

 

thanks!

M.Kierepka

Hi @CamdenMaggos,

Yes, there is a simple solution:

1. Create SYS Lookups module, with no dimensions and no formula, and add a line item A, with format of your List.
2. Pick New England for A.

3. Change your formula to P&L Revenue [LOOKUP: SYS Lookups.A] / P&L Revenue [Select: List.Total Company] (this second select is hard to avoid, but you will be able to use it even for production list, if it's Top Level item).

In fact, such use of Lookups instead of Selects should be used even if you have non-production list.

ryan_kohn

First off, this is one of the big reasons why we recommend to avoid using SELECT. If you need to need to get the value associated with a specific item, the better approach is to set up a System module with a list-formatted line item, and input the value in the module as data (not a formula). Then, you use a LOOKUP in your final formula to get the value associated with that item.

 

Second, it looks like you might be going down the path of replicating a lot of line items and using a series of SELECT statements. There is likely a more scalable approach if you dimension your module appropriately.

 

For the example you provided, you don't want to end up with a bunch of line items like:

"P&L Revenue - New England"
"P&L Revenue - Pacific Northwest"

etc.

 

That will make maintainance and tracability a pain.

CamdenMaggos

Thank you! This was exactly what I needed.