Sum a line item based on all items in a list

slicktrickrick
slicktrickrick
in Modeling

Hello,

I am attempting the following: I need to sum up a line item's values based on all of the list items used in the module. For example: the module I am building uses a list of adjustments as a dimension (ex: Adjustment % 1, Adjustment % 2, etc.), then there's a line item in the module for the percent amount for each adjustment, finally, there's a line item for sum of all adjustments. This sum of all adjustments simply needs to take the percent amount for each list item and sum it up to have a total adjustment amount across all list items in the module. I have attempted to use the SUM formula but it has not been successful. What can I do to sum up all my adjustments?

image.png

Answers

  • BenjaminNiel
    BenjaminNiel

    Hi @slicktrickrick,

    First, in your list of adjustments, you need to add a top level (General Lists → Top Level)

    image.png

    Then, in your module, you need to remove the adjustment dimension from the line item "Sum of Adjustments"

    image.png

    So, this line item will only have the time dimension

    Then, in the formula of "Sum of Adjustments", you can just reference the "Initial Adjustment Amount" (see screenshot above). It will automatically take the total of all the adjustments

    image.png

    Hope this helps!

  • slicktrickrick
    slicktrickrick

    @BenjaminNiel I will give this a try and see if it works! Thank you

  • slicktrickrick
    slicktrickrick

    Hey @BenjaminNiel,

    I attempted that solution and am getting the following result:

    image.png

    The initial adjustment amount does not appear to be summed. The adjustment list has a top level now ("all adjustments"), the "Sum of…" line item is only dimensionalized by the location (needed) not by the list anymore, and the line item references the "Initial Adjustment %" line item. There are two adjustments I have to test @ 10% each so that Sum should be showing 20%.

  • BenjaminNiel
    BenjaminNiel

    Hi @slicktrickrick,

    Nice that you gave it a try!

    2 quick checks:

    • can you check that the summary method of the "Initial Adjustment %" is a Sum? In case it is None, the top level will be empty
    image.png
    • what is the format of the line item "Sum of Adjustments"? If it's a normal number (not a %) with 0 decimal places, then 20% = 0.2 will be displayed as 0
    image.png

Categories