Correlation matrix

[GFire]

From the attached dataset, create the image matrix. Please.

Dataset Link:

https://docs.google.com/spreadsheets/d/1UpU-BSpuBebzvAelfOPYFwBqZZWTv085/edit?usp=sharing&ouid=11160...

[v-sgandrathi]

Hi @GFire,

Use this DAX Measure:

Correlation = 
VAR CurrentX = MAX(SalesData[Compare_Year])
VAR CurrentY = MAX(SalesData[Year])

VAR FilteredX = 
    FILTER(SalesData, SalesData[Compare_Year] = CurrentX)

VAR DataWithY = 
    ADDCOLUMNS(
        FilteredX,
        "Y", 
        LOOKUPVALUE(
            SalesData[Sales],
            SalesData[Year], SalesData[Year],
            SalesData[Compare_Year], CurrentY
        )
    )

VAR AvgX = AVERAGEX(DataWithY, SalesData[Sales])
VAR AvgY = AVERAGEX(DataWithY, [Y])

VAR Numerator =
    SUMX(DataWithY, (SalesData[Sales] - AvgX) * ([Y] - AvgY))

VAR Denominator =
    SQRT(
        SUMX(DataWithY, (SalesData[Sales] - AvgX)^2) *
        SUMX(DataWithY, ([Y] - AvgY)^2)
    )

RETURN 
    DIVIDE(Numerator, Denominator)

Additionally, I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.

Thank you.

[GFire]

I've looked at the .PBIX file, but it doesn't specify how the "SalesData" table was created. What's the DAX code to create it?

[v-sgandrathi · Accepted solution]

Hi @GFire,

Below is the DAX code used to create the SalesData table:

SalesData =
SELECTCOLUMNS (
ADDCOLUMNS (
CROSSJOIN (
VALUES('YourUnpivotedTable'[Year]),
VALUES('YourUnpivotedTable'[Year])
),
"Sales",
CALCULATE (
SUM('YourUnpivotedTable'[Sales])
)
),
"Year", [Value1],
"Compare_Year", [Value2],
"Sales", [Sales]
)

In this code, replace Replace 'YourUnpivotedTable' with the actual name of your unpivoted base table. This DAX statement generates a table displaying all combinations of Year and Compare_Year, together with their corresponding sales values. This serves as the basis for calculating the Pearson correlation using the measure I previously provided. If you would like me to incorporate this into the PBIX file and upload the updated version, please let me know.

Thank you.

[FBergamaschi]

If you do not give more specific about what you are looking for, it is impossible to help you

What are the nrs included in the matrix? 2000 in the cell (2017, 2017), what is that? What are the columns in rows and columns in the matrix?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[GFire]

Hi and Thank you for your attention. I hope this example can help you.