Formula help on finding a % of a Calculated LI

neg177
neg177
edited December 2022 in Modeling

Hi, 

 

I'm trying to create a formula where it will create a % based on taking my States List parent by each individual state. 

The formula I have so far is Total Collections / Total Collections[SELECT: State.North America] . I feel like this is correct but my percentages are not looking right. Please advise. 

 

want to take this amount 

neg177_0-1669147200190.png

and divide it by the total States List amount 

neg177_1-1669147272112.png

 

Thank you 

 

 

Tagged:

Answers

  • ShubhamCh
    ShubhamCh
    edited December 2022

    Hi @neg177 

    The formula is incorrect because it is only referring to North America total, which becomes wrong when the parent is other than NA.

    Right approach would be

    • Total Collections/ Total Collections[Lookup:SYS Module.Parent]

    I assume you have one SYS module for your child states where you've defined parents. If that's not the case, add one line item - "Parent" (Parent List Formatted), formula - Parent(Item(your current child state list name)) 

    Use this line item as your lookup mapping.

    It'll calculate as you want it to.

    Thanks!

    -Shubham

  • neg177
    neg177
    edited December 2022

    I'm not quit sure if I'm doing what you said correctly. Can you please check? 

    The formula for the Total Collections % doesnt work. 

    neg177_0-1669671932867.png

     

     

    Thank you 

  • andrewtye
    andrewtye
    edited December 2022

    Hi @neg177 

    Not sure if this is still hanging about but couple of things.

    1. Don't use "SELECT:" when referencing list items (unless this happens to be the very top-level of it), best practice is to have a look-up module and reference that as @ShubhamCh points out.

    2. The summary setting for this should be formula - now using lookup and formula clearly won't work so you'll need in intermediate line item that pulls back the relevant number using that parent line to look-up against (which btw should be in a property module - again best practice)

    Hope that helps!

Categories