Forum Discussion

Angus01's avatar
Angus01
Regular Visitor
2 years ago

Linestx and plotting a graph

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...
Need Help
Angus01's avatar
Angus01
Regular Visitor
2 years ago

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

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.