Create formula column at item level based on two subitem summarized columns

Platform discussions Special workflows & use cases
1

Hi Community,

I’m finding and issue after several tries, so I would kindly ask for any support here.

I have a board representing our selling contracts for Hotel Chains. Each of our contracts (item level) has N hotels (subitem level). Each hotel has two columns, “Number of rooms” and “Rate”.

I would like to create a column at item level that can provide the ratio “Rate per Room” (meaning SUM(Rates)/SUM(Rooms)).

I’ve followed two approaches but none of them worked:

Approach A - Create two summary columns at item level, “Total Rate” and “Total Room”, and then generate a 3rd column called “Rate per Room”. This gives an error saying that formulas do not allow multiple reflected columns.

Approach B - Create a colum at sub-item level called “Rate per Room”. After that, create a summary of that column at item level, and set the aggregation type to “Average”. The issue is that the average is not weighted, so it doesn’t provide the correct result.

Does anyone have a valid alternative here?

Thanks a lot in advance.

2

hi @a.alvarez

The summary column in the item can’t be used in formulas as it is basically a mirror column (not a real number column). With the app called Rollup Subitems you can “summarize” subitem info into the parent item, but in this case the parent column will be a real number column that can be used in formulas.

1 Like
3

Hi @basdebruin, thanks for your answer, nevertheless it’s sad that I had to upgrade to use formulas, and it’s not enough to perform simple calculations… the fact that we need to purchase 3rd party apps to accomplish a regular sum of two columns it’s really surprising. Hope monday.com fixes this at some stage.

Thanks again for taking your time to answer.