Calculate Total Taking Into Account Categories

[Rickmaurinus]

Hi,

For a test investment portfolio I use below data. It shows the ISIN number for the Fund. Note that there are several funds.

Then there's a column with the current stock value, the purchased amount of Stocks & the purchased stockprice.

To always have the latest stock value I need to know the CUMULATIVE QUANTITY I own from a certain stock. The formula for that is:

Purchase Quantity Cumulative = 
CALCULATE( [Purchase Quantity], 
                       FILTER( ALL ('Calendar'), 'Calendar'[Date] <= 
                                              MAX('Calendar'[Date])))

And I multiply that amount by the current Stockprice. 

Now that works fine when I use the Investment Fund as a legend, or as a column seperator. Unfortunately this does not work for calculating the total level of the portfolio.

After all, when I calculate the cumulative quantity that I've bought & multiply it by the total stockprice, it adds the quantity of all the different FUNDS & multiplies it by the stockvalue of all the three funds together. Can someone come up with a suggestion how I can calculate the total value of the portfolio, taking into account the data for the different funds?

I tried to come up with something like:

TotalCurrentStockValue = 
CALCULATE( [Purchase Quantity Cumulative] * [Current StockPrice Adj], 
                        AllPrices[ISIN] = VALUES( AllPrices[ISIN] ) 
                       )

This obviously didn't work out. 

[Rickmaurinus · Accepted solution]

Hi,

Sorry I've been on vacation. I have solved this issue by creating some calculating columns. After doing some research it seems that's the best option. My goal was to try to get the right numbers without the calculated columns. In the end I used the first two formulas as calculated column. And the last one as a measure. 

PurchaseQuantityCumulative = CALCULATE( [Purchase Quantity], FILTER( ALL(AllPrices), AllPrices[Date] <= EARLIER(AllPrices[Date])), AllPrices[ISIN] = EARLIER(AllPrices[ISIN]))

PortfolioValueCumulative = AllPrices[PurchaseQuantityCumulative] * AllPrices[CurrentStockValue]

Portfolio Value = CALCULATE(  SUM(AllPrices[PortfolioValueCumulative]), FILTER('Calendar', 'Calendar'[Date] = MAX('Calendar'[Date])))

 Thanks for responding! 🙂

Rick

[v-jiascu-msft]

Hi @Rickmaurinus,

Could you please tell me if you solved it? Can you share the answer or mark the proper answer as a solution, please?

Best Regards,
Dale

[v-jiascu-msft]

Hi @Rickmaurinus,

Seems you need a function that can provide row context like SUMX. Please try the formula below. 

TotalCurrentStockValue =
SUMX ( 'table', [Purchase Quantity Cumulative] * [Current StockPrice Adj] )

Best Regards,

Dale