Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
09-07-2017 06:17 AM - last edited 04-12-2018 23:19 PM
This measure allows you to predict dependent values Y from independent values X.
Simple linear regression
Estimate Y values based on X values.
Name: Category
Tooltip: The category for which you have known X and Y values
Type: Categorical field
Name: Measure X
Tooltip: Known X (independent) values
Type: Numerical field / measure
Name: Measure Y
Tooltip: Known Y (dependent) values
Type: Numerical field / measure
Estimated {Measure Y} =
VAR Known =
FILTER (
SELECTCOLUMNS (
ALLSELECTED ( {Category} ),
"Known[X]", CALCULATE ( {Measure X} ),
"Known[Y]", CALCULATE ( {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 * {Measure X}
For more details on and other uses of this quick measure, see my blog post on the subject:
https://xxlbi.com/blog/simple-linear-regression-in-dax/
eyJrIjoiZWNiNTQ2MGEtMjhlNS00YTNhLWE2NTktZDg3MzcxMjQ0NDc4IiwidCI6ImQzMmNkYzNmLTY1NTUtNGNhYy1iYjFhLTg2OWZiMTE0MzRlNSJ9
I reckon DAX might be cumbersome for this. You can consider installing python in Power BI to generate a linear model for your analysis. @jwjwjwjwjw
Daniil, (or anyone else knowledgeable)
Many thanks for this but I am struggling a little with adapting it to my needs, as I am still quite new to DAX.
I am trying to create a sales prediction model and want to use the linear trend from just the last 12 months, I believe your current code will give me the linear regression from all of the data.
How do a go about just using the last 12 months of data to feed the regression.
Many thanks for anyone that might be able to help,
Mark.
Hi Daniil
Thanks again for all your help. I have attached the whole file because it might interest you anyway. I sholuld stress it is a work in progress and my first use of Power BI. Please keep it to yourself.
The page labeled Tony Data with the table in it shows the problem clearly--although it does not reproduce in the chart on that page. It is not really a problem, but it would be nice for us to understand the behaviour.Play around with the date slider and you will notice that if you set the start date to 1960 only you see -32.72 at the top and bottom of the [Estimated Tony Data] column. Other start dates do not exhibit this problem.
I will take a look at the blog.
Tony
Aye Tony, hope my suggestion could work for you with such problem:
You can try replacing this part:
RETURN
Intercept + Slope * SELECTEDVALUE ( 'Tony_data'[YEAR])
with this one:
RETURN
SUMX( Tony_data ,
Intercept + Slope * SELECTEDVALUE ( 'Tony_data'[YEAR])
)
Btw, this one is the very first reply of mine in this place, so HELLO WORLD then :'>