Summary over Line Item Subset

dsterbakova
Occasional Contributor

Summary over Line Item Subset

Hello all,

I need to calculate a sum of costs from P&L which are base for subsequent calculation of revenue (sum of costs * (1+margin%)). The tricky part is that we need dynamic setting for identifying the costs to be aggregated because each year different cost might be considered as part of the base. I would therefore like to avoid extensive use of IF function and hardcoding.
Instead, I would like to do that using Line item subset (LIS) which is easy to configure. I wanted to go about this in the following way which unfortunately does not work because LIS does not have a top level.

Step 1: Collect the costs from P&L module [dimensions: Departments, Geography, LIS, Months]

Step 2: Sum up the costs to calculate the Base [dimensions: Departments, Geography, Months] 

Do you know of possible workaround?
Tagging @JaredDolich @Misbah @rob_marshall in case if you have any suggestions. 

1 ACCEPTED SOLUTION

Accepted Solutions
Misbah
Moderator

Re: Summary over Line Item Subset

@dsterbakova 

 

See if this helps

 

Step 1: Create a LIS - I assume you have already created

Misbah_0-1634112867985.png

Step 2: Create a List mimicking LIS

Misbah_1-1634112900202.png

Step 3: Create a SYS module for the mapping between LIS from Step 1 and List from Step 2

Misbah_2-1634112938336.png

Step 4: Use SUM function to aggregate the values from source module. Target module will have "Expenses List" as a dimension

Misbah_3-1634112977689.png

This process basically allows you to aggregate the values out of LIS because there is no TOP Level for LIS

 

Misbah

 

View solution in original post

1 REPLY 1
Misbah
Moderator

Re: Summary over Line Item Subset

@dsterbakova 

 

See if this helps

 

Step 1: Create a LIS - I assume you have already created

Misbah_0-1634112867985.png

Step 2: Create a List mimicking LIS

Misbah_1-1634112900202.png

Step 3: Create a SYS module for the mapping between LIS from Step 1 and List from Step 2

Misbah_2-1634112938336.png

Step 4: Use SUM function to aggregate the values from source module. Target module will have "Expenses List" as a dimension

Misbah_3-1634112977689.png

This process basically allows you to aggregate the values out of LIS because there is no TOP Level for LIS

 

Misbah

 

View solution in original post