Re: Carry formula into summary row?

722 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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.

1 Reply 1

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:

Screenshot 2023-03-01 at 5.42.33 PM.png

Screenshot 2023-03-01 at 5.42.23 PM.png
Link to base

And you'd use automations to create the links you need like so:

Screenshot 2023-03-01 at 5.44.19 PM.png