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.JPGAnaplan LP List.JPGAnaplan LP PI List.JPGLP Alliance PI.JPG

 

Best Answer

  • Rashmih
    Answer ✓

    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

Answers

  • 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]

    Photo1.png

     

    ~Vignesh

     

  • @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

     

  • 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"? 

  • @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