Feb 28, 2023 11:11 AM
I am building a database for an investment firm where they purchase and sell investments. I have a column that calculates the multiple earned on an investment. For example, if cost = $100; proceeds on sale = $150 (proceeds / cost) then the multiple for this investment is 1.5x.
Each investment's multiple is calculating just fine but I cannot figure out a way to calculate the firm's total multiple as it is not simply a sum of the multiples, it has to be recalculated at the firm level (i.e. sum of all proceeds on sale / sum of all costs). I want to be able to calculate this number based on pivots/groups/filters. For example if I wanted to know the multiple based on investments made in a certain geography or industry.
Attached is a screenshot for the number that I need to calculate at the firm level. Any ideas? I would love to just carry the formula that is in the column to the summary row.
Mar 01, 2023 01:44 AM
To do this I'm afraid you're going to need to create a new table to do all the summaries for you, something similar to this:
Link to base
And you'd use automations to create the links you need like so: