Department as a % of Total Revenue

CamdenMaggos
Contributor

Department as a % of Total Revenue

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!

1 ACCEPTED SOLUTION

Accepted Solutions
M.Kierepka
Certified Master Anaplanner

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.

View solution in original post

3 REPLIES 3
ryan_kohn
Certified Master Anaplanner

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.

M.Kierepka
Certified Master Anaplanner

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.

CamdenMaggos
Contributor

Thank you! This was exactly what I needed.