I have a table I would like to use as an estimator. I would like the parent row to gather the totals of the Sub Item rows and apply that to the parent row calculation. What is the best way to accomplish this?
Have you considered grouping by parent row without subitems and using the summarize function? I think that you could get something similar to what you want that way.
But if you really want to keep everything in a single table and make use of the subitems, I can only offer you this not very elegant approach.
Assuming that you only have 1 level of subitems and only those contain raw data, you would need 2 columns for each of the fields you want to calculate:
- Raw data: Empty for all items that are not subitems
- Calculated: If(thisRow.Subitems.IsBlank(),thisRow.[Raw Data],thisRow.Subitems.[Raw Data].Sum())
Hide all the ‘Raw data’ columns and create a conditional form rule to make parent rows bold.
Hope this helps,
Pablo
I actually found the solution in another thread.
This formula calculates the total of all the sub items in under the parent row as another column. I then changed my client and cost totals to include the subitem total plus the calculated total.
It works pretty nice for an estimator.