Forum Discussion
Correlation matrix
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.
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.
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?
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.
