Forum Discussion

pedroluccas's avatar
pedroluccas
Frequent Visitor
3 years ago

LINEAR REGRESSION - X-AXIS IN TEXT FORMAT

Hello I am working with a data sample that contains numeric values on "y" axis (pH results) and text values on "x" axis (sample/batch code) and need to build a linear regression analysis. So far, I...
Statistics
Trend Line
Variable calculation
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

pedroluccas So, duplicate your query in Power Query Editor. Remove all rows except the SAMPLE CODE column. Do a Remove Duplicates. Create an Index column (Add Columns | Index). You can then either do a merge to get your index or in a DAX calculated column, add a column to your original table that grabs the Index using RELATED.

 

You can find sample code here:

Correlation coefficient - Microsoft Power BI Community

If you have seasonality in your data:

De-Seasonalized Correlation Coefficient - Microsoft Power BI Community

  • pedroluccas's avatar
    pedroluccas
    Frequent Visitor
    3 years ago

    Thank you very much!

    It worked for Slope and R². But now seems that I have other problem.

    When I have a huge amount of samples of different products: A1AA/22 (Product A), B1AA/22 (Product B), C1AA/22 (Product C), etc, it generates a index with huge numbers (10,000 or more).

    Other scenario is when I wnat to use just an interval of the samples (sample 5 - 12).

    Since Intercept formula is b = [(Σy)*(Σx²) - (Σx) * (Σxy)] / [n * (Σx²) - (Σx)²], and now appears the factor (Σx²) on the numerator, huge numbers seems to affect the calculation.

     

    See below the calculation and graphs

     

     

    What can I do to make the index change according with my selection and restart the count with the number 1?