Forum Discussion

cyuan's avatar
cyuan
Frequent Visitor
2 years ago

1 column data correclatio coefficient

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

 

ProductCustomerMonthSales
A111A1/07/20231106
A111A1/08/20233851
A111A1/09/2023370
A111A1/10/20231355
A111A1/11/20232179
A111A1/12/20233610
A111A1/01/20243592
A111A1/02/20243616
A111A1/03/20242246
A111A1/04/20243443
A111A1/05/20242659
A111A1/06/20242463
A111A1/07/20243106
B222B1/07/2023946
B222B1/08/20231222
B222B1/09/20233622
B222B1/10/20233629
B222B1/11/20232870
B222B1/12/20233082
B222B1/01/20241350
B222B1/02/20242271
B222B1/03/2024675
B222B1/04/20242424
B222B1/05/20242517
B222B1/06/20241378
B222B1/07/20241964
C333C1/07/20231702
C333C1/08/20231915
C333C1/09/20232401
C333C1/10/20233128
C333C1/11/2023208
C333C1/12/20231711
C333C1/01/20242355
C333C1/02/20242789
C333C1/03/2024739
C333C1/04/20242399
C333C1/05/20242724
C333C1/06/20243142
C333C1/07/2024417

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • cyuan's avatar
    cyuan
    Frequent Visitor
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        cyuan
        Frequent Visitor
        2 years ago

        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

         

         

        )