1 column data correclatio coefficient

[cyuan]

Hi all, 

i would like to measure the correlation between different SKUs with sales history.

i have attached the sample data, they are all in one table.

can you please help?

CY

Product	Customer	Month	Sales
A111	A	1/07/2023	1106
A111	A	1/08/2023	3851
A111	A	1/09/2023	370
A111	A	1/10/2023	1355
A111	A	1/11/2023	2179
A111	A	1/12/2023	3610
A111	A	1/01/2024	3592
A111	A	1/02/2024	3616
A111	A	1/03/2024	2246
A111	A	1/04/2024	3443
A111	A	1/05/2024	2659
A111	A	1/06/2024	2463
A111	A	1/07/2024	3106
B222	B	1/07/2023	946
B222	B	1/08/2023	1222
B222	B	1/09/2023	3622
B222	B	1/10/2023	3629
B222	B	1/11/2023	2870
B222	B	1/12/2023	3082
B222	B	1/01/2024	1350
B222	B	1/02/2024	2271
B222	B	1/03/2024	675
B222	B	1/04/2024	2424
B222	B	1/05/2024	2517
B222	B	1/06/2024	1378
B222	B	1/07/2024	1964
C333	C	1/07/2023	1702
C333	C	1/08/2023	1915
C333	C	1/09/2023	2401
C333	C	1/10/2023	3128
C333	C	1/11/2023	208
C333	C	1/12/2023	1711
C333	C	1/01/2024	2355
C333	C	1/02/2024	2789
C333	C	1/03/2024	739
C333	C	1/04/2024	2399
C333	C	1/05/2024	2724
C333	C	1/06/2024	3142
C333	C	1/07/2024	417

[cyuan]

Hi ZD,

thanks for reply. I couldn't upload a sample file, basically I have a column of prodcut and one column of customer, column of month of sales, then another column of sales in units.

what I try to do is to work out a way to calculate correlation coefficient for one customer between all products with certain period of historical sales. 

[Anonymous]

Hi @cyuan 

You can get this result by using the pivot column in Power Query:

Here are steps:

First of all, Select the Product column and click the Pivot column in the Transform pane:

The result is as follow:

But I can't understand how to get the 2%, 24% and -34%, if possible please share the logic of these results. 

Best Regards

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

[cyuan]

Thanks ZD for prompt reply.

the % is calculated in Excel with forumla Correl(array1, array2)

I try to use the dax quick measure correlation coeffecient however it ask for 2 columns, in this case it does't really help because i will have more than 2 columns. 

thanks

)

[Irwan]

Hello @cyuan 

Not sure if this match to your need but please have a look.

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-Help-on-Autocorrelation-Calcula...

That was my post and answered by @lbendlin. The DAX works perfectly.

Thank you.

[cyuan]

thanks Irwan, not precisely, @Ibendlin DAX is autocorrelation and it is a tool to look at correlation for one variable but different lag, it should eventually return to 0. What I am after is 1 v 1 correlation based but multiple comparison.

thanks anyway,.

[Anonymous]

Hi @cyuan 

Actually, I can't quite understand what you need.

If possible, please attach the expected result with backend logic and special examples? It would be helpful to find out the solution.

Best Regards

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