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.
