Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Matrix multiplication using measures

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:

idvalue
10921081  0.103
111611010.052
118511220.061

 

correlation:

id_rowid_columnvalue
10921081  10921081  1
10921081111611010.37
10921081118511220.08
11161101109210810.37
11161101111611011
11161101118511220.29
11851122109210810.08
11851122111611010.29
11851122118511221

 

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.

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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.

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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