Linestx and plotting a graph

[Angus01]

I have used the linestx function to acquire both the intercept and slope values for some data. I would now like to add some what if analysis and see this plotted on a graph. When I display the values on cards and transpose the actual figures to an equation y=mx+b, the line is created perfectly fine. I would obviously like to refer to the columns in the Linest table - but this doesn't seem to work. Whether I create the table within the measure and select the relevant cols via SELECTCOLS or create a new table using the LINESTX function - the resulting graph is not what I expect. 

for reference on the below picture, my code for each line: 

only measures = 

var reg_table = LINESTX('COOISPI Orders', [ysum], [xsum])

var slope = SELECTCOLUMNS(reg_table, [Slope1])

var intercept = SELECTCOLUMNS(reg_table, [Intercept])

return

(intercept + slope*'date predictor'[date predictor Value])/13*'Hours per day'[Hours per day Value]

referencing external reg table = 

(AVERAGE('regression line'[Intercept]) + AVERAGE('regression line'[Slope1])*'date predictor'[date predictor Value])/13*'Hours per day'[Hours per day Value]

total per day = 


(16255.73*AVERAGE('Calendar Table'[index])-58970)/13*'Hours per day'[Hours per day Value]

[Angus01]

as an update - I managed to get this to work with the referenced intercept and slope values from the LINEST function using the below code (stolen with pride from a tutorial) but I don't understand why this works and my previous attempts failed. I would really appreciate someone explaining where I went wrong. Have a great weekend all!

var known = 
FILTER(
    SELECTCOLUMNS(
        ALLSELECTED('Calendar Table'[index]), 
        "Known[X]", 'Calendar Table'[index], 
        "Known[Y]", [ysum]
    ), 
    AND (
        NOT ( ISBLANK ( Known[X]) ), 
        NOT ( ISBLANK ( Known[Y]) )
    )
)

VAR SlopeIntercept = 
LINESTX(Known, Known[Y], Known[X])
VAR Slope = 
SELECTCOLUMNS(SlopeIntercept, [Slope1])
VAR Intercept = 
SELECTCOLUMNS(SlopeIntercept, [Intercept])

RETURN
SUMX(
    DISTINCT('Calendar Table'[index]), (Intercept + Slope*'Calendar Table'[index])/13*'Hours per day'[Hours per day Value]
)

[Anonymous]

Hi @Angus01 

Since I don't have your data, I can't reproduce your situation. Right now I can only make suggestions based on your formulas, and you can create a calculated table first to see what this part of the formula returns.

FILTER(
    SELECTCOLUMNS(
        ALLSELECTED('Calendar Table'[index]), 
        "Known[X]", 'Calendar Table'[index], 
        "Known[Y]", [ysum]
    ), 
    AND (
        NOT ( ISBLANK ( Known[X]) ), 
        NOT ( ISBLANK ( Known[Y]) )
    )
)

If there are still questions, please feel free to ask me.

Best Regards,
Yulia Xu

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