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!

Accepted answers

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.

All comments

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.

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.

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

CamdenMaggos

Thank you! This was exactly what I needed.

Quick Links

Can you help?

Unanswered questions

Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…