Hi,

I have a column which is made up from a List. I've created a "new column" called "Sales and Marketing" and would like to sum up the values that are currently in "Sales" and "Marketing" respectively. The values in the other columns remain as it is.  Is there a formula that is able to do this?

Structure of the module as below.

Tagged:

Hi @DarrenS ,

Although, I would recommend following Parent-Child relationship and then adjusting the downstream calculations, here is the alternate way which you might be looking for (if there are intricacies which prevent the use of parent-child arrangement in your case)

You can sum the values in Sales, Marketing by using a property (Parent item) as shown below:

Data View

Blueprint View

The formula in lineitem: Output is using Admin Settings module. This is to avoid hard-coding in the formula:

These 3 lineitems in Admin Settings are LOB list formatted.

Hope this helps!

Best Regards,

vinayvm

• Hi @DarrenS ,

You can achieve this by having the list elements: Sales, Marketing as child items of 'Sales & Marketing'. This arrangement will automatically sum the values at the parent level, without having to use a formula.

Below is a screenshot for your reference.

Hope this helps!

Best Regards,

vinayvm

•

Thanks for your help. It works! 🙂

But is there another method using a formula? Reason being, after i've made the amendment on the list, there is a line item which uses this LOB list and the values are kind of wrong now.

•

Regards to below solution you had provided earlier, I just realized an issue with the total sum of All LOBs.

Due to the hierarchy list of LOB, the total sum of LOB is counting the "Sales" and "Marketing" figures as well.

Is there a way to exclude double counting the respective "Sales" and "Marketing" figures. I will be hiding the "Sales" and "Marketing" columns and the "All LOBs" should be showing 325 instead of 525.

•

I like to go back to the initial recommendation of using parent-child relationships which would automatically sum up the values at top level.

However, a quick workaround would be to introduce a dummy list item and use it as follows:

> You can hide the Dummy list item in the page as needed

> Also, you might want to use a Line item Subset with items other than Dummy item in downstream modules to prevent users from accessing this item (to avoid confusion)

Regards,

vinayvm

•

Thanks for this.

How do you set up the Line item Subset with items other than Dummy item and what do you meant by downstream modules?

•

Sorry, I meant list subset but ended up typing Line item subset : P

The list subset will contain all list items apart from the dummy item, and you can use this list subset as a dimension in your modules instead of the whole list (to keep users from getting confused)

Regards,

vinayvm

•

Do I create a subset list that contains "Other, Supply Chain, HR, Projects, Finance, Sales and Marketing" or "Other, Supply Chain, HR, Projects, Finance, Sales, Marketing, Sales and Marketing". It should be the first one right?

With the subset list, does it mean that it will just gives us the total sum of the subset list rather than the whole list? Is there still a need  to create the dummy line item?

•

- Inclusion of Sales, Marketing, Sales & marketing in the subset will depend on your objective/process. If you want users to input values, you might want to include just the first two from the above. Whereas If it is a report, you might want to include all the 3.

- The purpose of list subset is that you will be able to prevent users from seeing the 'Dummy' item and getting confused, Also, the Dummy line item in your module will be necessary if you want the total to match at 'All LOB' level.

Regards,

vinayvm