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.