Portfolio Variance - matrix multiplication

[badger007]

Hi guys,

Need your help.

I have a portfolio with various accounts each of which has some products (called risk_curve_month_name in my datasets).

My goal is to be able to calculate portfolio variance based on what accounts were selected (one or a few).

Portfolio variance is calculated through the multiplication of the transposed vector of asset weights by the variance-covariance matrix and multiplied by the vector of asset weights again (this time not transposed).

The weights of assets are already calculated in my power bi: quantity (called exposure in my dataset) * price * variance.

The tricky part is that the variance-covariance matrix consists of all possible combinations of the products. However, some accounts only have a couple of products. So before doing the matrix multiplication, the variance-covariance matrix must be filtered first.
Below are snips of my power bi and my example calculation in excel using MMULT and Transpose functions (see the formula bar on the snip).

It's probably confusing. So please ask me a question if anything is unclear.

Many thanks for your time and help. Hope you can help me 🙂

[Ann1234]

Hi @badger007 ,

I have the same question, have you found a solution for this yet? 

Thanks

[badger007]

Hi,

Unfortunately no. I use a python code but it is so slow. I'm sure that using the measures would make it 10 times faster.

[v-yangliu-msft]

Hi  @badger007 ,

You can try the following function on variance:

Population Variance: calculated using the entire data set with the VAR.P function.

VAR.P function (DAX) - DAX | Microsoft Learn

VARX.P function (DAX) - DAX | Microsoft Learn

Sample Variance: calculated using a subset or sample of the data set with the VAR.S function. This is typically used when it is not feasible to analyze the entire data set.

VAR.S function (DAX) - DAX | Microsoft Learn

VARX.S function (DAX) - DAX | Microsoft Learn

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.