Matrix multiplication using measures

[Anonymous]

Hello everyone,

I am new to DAX and I need urgent help. I have two datasets (vol and correlation) that I am pulling from my company's cloud database. Sample data are as follows:

vol:

id	value
10921081	0.103
11161101	0.052
11851122	0.061

correlation:

id_row	id_column	value
10921081	10921081	1
10921081	11161101	0.37
10921081	11851122	0.08
11161101	10921081	0.37
11161101	11161101	1
11161101	11851122	0.29
11851122	10921081	0.08
11851122	11161101	0.29
11851122	11851122	1

I need to create DAX measures to auto-compute the variance-covariance matrix, but I am unsure how this can be done in DAX. I have created 2 tables (C1 and C2) which contains the id_rows and id_columns. I would be using the matrix visual to display the variance-covariance matrix, something similar to the one shown below: 

covariance matrix

Any help is much appreciated.

[Anonymous]

Thanks @bhanu_gautam! Your solution is perfect.

Out of curiosity, if for some apparent reason I need to sum up the covariance measure to show 1x1 value so that it can fit on a card visual (look at images below), may I know how it can be done?

i.e., Sum up all the values in covariance values in the matrix

So that the summed value can be shown in the card

[bhanu_gautam · Accepted solution]

@Anonymous Ensure that you have relationships set up between your vol and correlation tables based on the id columns.

First, create a measure to calculate the variance for each id in the vol table.

dax
Variance =
VAR CurrentID = SELECTEDVALUE(vol[id])
RETURN
CALCULATE(
SUMX(
vol,
IF(vol[id] = CurrentID, vol[value]^2, 0)
)
)

Next, create a measure to calculate the covariance using the correlation table and the variance measure.

dax
Covariance =
VAR CurrentIDRow = SELECTEDVALUE(correlation[id_row])
VAR CurrentIDColumn = SELECTEDVALUE(correlation[id_column])
VAR VolRow = CALCULATE(SUM(vol[value]), vol[id] = CurrentIDRow)
VAR VolColumn = CALCULATE(SUM(vol[value]), vol[id] = CurrentIDColumn)
VAR CorrelationValue = CALCULATE(SUM(correlation[value]), correlation[id_row] = CurrentIDRow && correlation[id_column] = CurrentIDColumn)
RETURN
VolRow * VolColumn * CorrelationValue

Add a Matrix visual to your Power BI report.
Place id_row from the correlation table in the Rows.
Place id_column from the correlation table in the Columns.
Place the Covariance measure in the Values.