Hi @KayKay,
Well, this took quite a bit of time to investigate. 
If I understood you correctly.
PT1. You have some kind of list Service Types and Categories, Categories are a part of Service Types. They represent the services which can be offered to a client.
BTW the naming is a bit misleading so I decided change it a bit: in my example below I will use Types and Functions (which are part of a Type).
So there is a full list of all Types with Functions, it can look like this:
Also you have ratios for each type. They show how many %% of a total budget can be spent for each Type. E.g. in a full list (like above), only 30% can be spent on Beauty type.
Inside the Types you have Functions (a detailed activities for a type). These Functions also have ratios, which mean that only certain %% of a Type’s budget can be allocated to this Function. E.g. for a full list, in a Beauty type, only 50% of a Beauty budget can be allocated to Spa procedures.
Q1: is my understanding correct?
PT2. What should happen if only some of the Types are ordered, and only some Functions in these Types are ordered.
As I understood, the ratios should be recalculated using the following logic:
- ratios for Types should be recalculated and normalized based only on Types included in the order.
E.g. in the example above, if we are not using Decor at all, the resulting ratios for Beauty and Food will be: Beauty = 0.4, Food = 0.6. They are calculated as:
actualBeautyRatio = baseBeautyRatio / (baseBeautyRatio + baseFoodRatio) = 0.3 / (0.3+0.45) = 0.4. - ratios for Functions inside types should be recalculated using similar logic - using “base ratios” only of those Functions which are selected.
- (important) budget calculation is a 2-step process: first the budget is allocated for each Type (using calculated Type ratios), and then in each Type the budget is split between selected Functions.
This is an example spreadsheet with only some Types and Functions selected:
PT3. So in order to calculate the Function’s ratio of a total budget, you will need to use the formulas:
actualCurrentTypeRatio = baseCurrentTypeRatio / sum (all selectedTypeRatios)
actualCurrentFunctionRatio = baseCurrentFunctionRatio / sum (all selectedFunctionRatios)
functionRatio=actualCurrentFunctionRatio * actualCurrentFunctionRatio
In the example above, in order to calculate how much %% of the budget client will spend on Hairdresser, you need to calculate:
actualHairdresserRatio = 0.3333 * ( 0.3333 + 0.16666) = 0.66666
actualBeautyRatio = 0.3 / (0.3+0.45) = 0.4
functionRatio = 0.2666666666.
So the client will spend approx. 26.6% of the budget on Hairdresser.
The problem is that you cannot just store this calculated number in the collection record easily.
Both ratios and amounts for ALL records may need to be recalculated EVERY TIME when you add or remove a record.
I’ve created a sample app and here is the video description how ratios and amounts change: Loom | Free Screen & Video Recording Software | Loom.
Best,
Victor.