context:
I have a table ‘offers’
with a subtable ‘offer lines’
an offer line has these columns ‘service’, ‘budget’, ‘year’
So an offer has multiple lines (for example)
interior 10,000 2022
branding 5,000 2022
interior 8,000 2023
product 6,000 2023
my goal is to have a list of service with the total budget over all the years
and a list of years with the total budget for that year (for all services)
I created a column (services) in offers with
thisRow.[Offer lines].Service.Unique()
this gives me a list of services (without duplicates)
I also created a column (services budget) in offers with
FormulaMap(thisRow.departments,CurrentValue.ToText()+’ '+thisRow.[Offer lines].Filter(Department.ToText()=CurrentValue.ToText()).[EUR budget].Sum())
I get a nice list of departments, but the budget after each department is the total budget (of all services). For some reason the filter is not working.
So if someone could help me to find the error in the formula?
Or if someone has a better idea to do this?
I don’t want to create a column for each service, since we keep adding services, and this would mean adding columns.
also for years that would mean adding a column each year.
I also tried to ad a subtable with a row for each service or year, but I can’t figure out to have a row for each department (and if an offer changes over time, with extra or fewer departments) it doesn’t update.
So a list in a row seemed the perfect way to do this. (downside is that I can’t use these numbers in other formulas, like total budget for a service in one year over all offers). But I could do that with a new formula from scratch.
THX