Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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