Need Formula Help

jdeluca

Hi all -

I am very new to Anaplan and have just finished the Intro to Model Building. As a test for me before going into Intermediate Model Building, I tried to set up a copy of a report I currently have in Excel.  The report is to show revenue by PI for each month & the % of their revenue at the site they are located.  I have uploaded in January data for the revenue & have the Anaplan report that shows the site total & revenue by the associated PI, but am stuck on how to get the % of revenue.  Below are screen shots of my List Set-up, the PI List, the Anaplan report & the Excel report I'd like to replicate.  All help is greatly appreciated.

 

Rashmih

Hi,

@jdeluca 

 

You can create a lineitem which brings the parent of 'PI LP3' ( Parent = Parent(Item('PI LP3' )) ).

Then use this lookup in the Revenue% formula ( Revenue% = Revenue / Revenue[LOOKUP: Parent])

Hope this helps.

 

Thanks and regards,

Rashmi

VIGNESH.M

Hi,

 

You are trying to calculate the Revenue % contribution by each L3 Item,

To get the revenue%, divide Revenue "PI L3" by "PI L3" parent

 

In your "PI L3", Add property as Parent L3

Parent L3 property format should be "L1", Logic parent(item('PI L3'))

 

Use this Formula in REV % Line item

= Revenue/ Revenue{lookup: PI L3.Parent L3]

 

~Vignesh

 

DavidSmith

@jdeluca @Rashmih @VIGNESH.M 

Just to add that the it is best practice to house the PARENT(ITEM('PI LP3')) logic (as well as the Site and Subsite properties)  in a module solely dimensioned by PI LP3 and not a list properties

 

It is also best practice to create a composite list so the parent lists are separate lists, not part of the PI LP3 hierarchy

 

David

 

hendersonmj

Hi David, 

I understand the value of creating Properties modules dimensioned by the list. (Better formula editor, better visibility to trace precedents and dependents, ...) 

 

In the DISCO categorization scheme, do you consider these properties modules as Data or System modules?  Or perhaps "In a Venn Diagram, they are a bit of each. Let's not get too rigid"? 

DavidSmith

@hendersonmj 

 

100% system module.  Think of anything that is the "glue" to join strucutures together.  Attributes of lists are not really Data in the truest sense, so we would classify the modules solely dimensioned by the list as System modules

Hope that clarifies things

David