Problems with Simple Linear Regression with DAX

[Syndicate_Admin]

Hi community, I am trying to calculate with DAX a simple linear regression but I have had different problems that have made it difficult to find a solution.

For context, I have a table that contains two columns, the first is "Years of seniority" which refers to the years of seniority of a worker and secondly I have a column "Average of tickets", which refers to the average requests that a worker makes with certain years of seniority.

As an example, in the table below you can see that a worker with 1 year of seniority sends on average 7 tickets or requests, a worker with 10 years of seniority sends on average 8 tickets or requests.

If we plot this particular case and add a trend line in Excel or power BI it would look something like this

There is a simple way to calculate linear regression on the internet, but it didn't work for me.

The steps are to create 2 measures:

xsum = SUMX('Regresión_simple','Regresión_simple'[Years old])

ysum = SUMX('Regresión_simple','Regresión_simple'[N° average tickets])

Consider a categorical column (which allows you to link the two columns

and then create the following measure:

Regression =

VAR Known =

FILTER (

SELECTCOLUMNS (

ALLSELECTED ( Table[Column] ),

"Known[X]", [Measure X],

"Known[Y]", [Measure Y]

),

AND (

NOT ( ISBLANK ( Known[X] ) ),

NOT ( ISBLANK ( Known[Y] ) )

)

)

VAR Count_Items =

COUNTROWS ( Known )

VAR Sum_X =

SUMX ( Known, Known[X] )

VAR Sum_X2 =

SUMX ( Known, Known[X] ^ 2 )

VAR Sum_Y =

SUMX ( Known, Known[Y] )

VAR Sum_XY =

SUMX ( Known, Known[X] * Known[Y] )

VAR Average_X =

AVERAGEX ( Known, Known[X] )

VAR Average_Y =

AVERAGEX ( Known, Known[Y] )

VAR Slope =

DIVIDE (

Count_Items * Sum_XY - Sum_X * Sum_Y,

Count_Items * Sum_X2 - Sum_X ^ 2

)

VAR Intercept =

Average_Y - Slope * Average_X

RETURN

Intercept + Slope * [Medida X]

Where bold codes should be replaced as follows

Table[Column] --> 'Regresión_simple'[Cat_ant]

[Measure X]--> xsum

[Measure Y]--> ysum

Unfortunately, graphing this measure gives me the same data as graphing the column of "Years old" and "Number of average tickets".

I would like to know if you can identify the error in using this measure or if you have another measure that allows me to do simple linear regression through DAX.

[Greg_Deckler · Accepted solution]

@Syndicate_Admin Maybe:

Solved: Simple Linear Regression with DAX - Microsoft Power BI Community

[Syndicate_Admin]

Thank you very much, it worked perfectly for me!!!