Forum Discussion

MKPartner's avatar
MKPartner
Icon for Helper II rankHelper II
9 months ago

Linear regression issue - straight line on the graph

Hi All,    I have an issue with linear regression line. I have changed the source of my data from manualy populated excel spreadsheets into data from SQL server. Due to this I have changed relation...
Need Help
Show and Tell
JamieHolding's avatar
JamieHolding
Icon for Resolver I rankResolver I
9 months ago

From a first look, I think it may be because of treating WC_Month as a value (would be a very high value around 40,000) which means it doesn't vary much across the x axis. 

Proposed this to AI and it suggested this as an alternative

COQ12M_TREND = 
VAR Known =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( 'COQ Calendar', 'COQ Calendar'[WC_Month] ),
            "Index", RANKX ( ALL ( 'COQ Calendar'[WC_Month] ), 'COQ Calendar'[WC_Month], , ASC ),
            "Y", [COQ12M_v2]
        ),
        NOT ( ISBLANK ( [Y] ) )
    )
VAR Count_Items = COUNTROWS ( Known )
VAR Sum_X = SUMX ( Known, [Index] )
VAR Sum_X2 = SUMX ( Known, [Index] ^ 2 )
VAR Sum_Y = SUMX ( Known, [Y] )
VAR Sum_XY = SUMX ( Known, [Index] * [Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X ^ 2,
        0
    )
VAR Intercept = AVERAGEX ( Known, [Y] ) - Slope * AVERAGEX ( Known, [Index] )

VAR MaxDate = MAX ( 'COQ Calendar'[WC_Month] )
VAR MinDate = DATE ( YEAR ( MaxDate ), MONTH ( MaxDate ) - 12, DAY ( MaxDate ) )
VAR SelectedMonths = 
    FILTER ( 
        'COQ Calendar', 
        'COQ Calendar'[WC_Month] > MinDate && 'COQ Calendar'[WC_Month] <= MaxDate 
    )
VAR MaxIndex = COUNTROWS ( SelectedMonths )

RETURN
    Intercept + Slope * MaxIndex