Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
5 years ago
Solved

Problems with Simple Linear Regression with DAX

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.

AlexisCab1602_2-1629844538502.png

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

AlexisCab1602_1-1629843277817.png

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".

AlexisCab1602_4-1629845109211.png

AlexisCab1602_5-1629845123483.png

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.