Need Formula Help

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.

Anaplan LP Aliance Rpt.JPG Anaplan LP List.JPG Anaplan LP PI List.JPG LP Alliance PI.JPG

Anaplan_LP_Aliance_Rpt_41873.JPG

Anaplan_LP_List_41873.JPG

Anaplan_LP_PI_List_41873.JPG

LP_Alliance_PI_41873.JPG

Accepted answers

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

All comments

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

Photo1_41887.png

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

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…