INF: How to Use the Linear Regression Functions

This article explains and illustrates the use of the following Linear Regression functions in a calculated member:

• LinRegIntercept
• LinRegPoint
• LinRegR2
• LinRegSlope
• LinRegVariance

Linear Regression is a statistical method that predicts the value of one variable, based on knowledge of another variable and the statistical relationship between the two. The Linear Regression functions use the method of least squares to calculate the equation of the best-fit line for a series of points. The general equation for linear regression is

Y'=aX+b

where Y' is the predicted value, a is the slope of the regression line, b is the intercept of the regression line, and X is a value of the X axis.

LinRegIntercept

The LinRegIntercept function returns the intercept of a regression line. In this example, you add a calculated member named "Intercept" to the Sales cube of the FoodMart sample. To add the calculated member, use these steps:

1. Open the Sales cube of the FoodMart sample for editing.
2. Add a new calculated measure with the following properties:
   • Parent Dimension = Measures
   • Member Name = Intercept
   • Value = LinRegIntercept(LastPeriods(10),[Measures].[Unit Sales],[Measures].[Store Sales])

LinRegSlope

The LinRegSlope function returns the slope of a regression line. In this example, you add a calculated member named "Regression Slope" to the Sales cube of the FoodMart sample. To add the calculated member, use these steps:

• Add a new calculated measure with the following properties:
  • Parent Dimension = Measures
  • Member Name = Regression Slope
  • Value = LinRegSlope(LastPeriods(10),[Measures].[Unit Sales],[Measures].[Store Sales])

LinRegPoint

The LinRegPoint function returns a predicted value of one variable, based on the statistical relationship between the variables. In this example, you add a calculated member named "Predict" to the Sales cube of the FoodMart sample. To add the calculated member, use these steps:

• Add a new calculated measure with the following properties:
  • Parent Dimension = Measures
  • Member Name = Predict
  • Value = LinRegPoint([Measures].[Unit Sales],LastPeriods(10),[Measures].[Unit Sales],[Measures].[Store Sales])
  • Format String = Standard

An alternate approach to returning a predicted value of one variable, based on the statistical relationship between the two sets of variables is to use the values returned by the LinRegIntercept and LinRegSlope functions. In this example, you add a calculated member named "Predict Formula" to the Sales cube of the FoodMart sample. To add the calculated member, use these steps:

• Add a new calculated measure with the following properties:
  • Parent Dimension = Measures
  • Member Name = Predict Formula
  • Value = ([Measures].[Slope]*[Measures].[Unit Sales])+[Measures].[Intercept]
  • Format String = Standard

Click the Data tab and compare the values you see in the Predict and Predict Formula columns. The values in the two columns should be identical.

LinRegR2

The LinRegR2 function returns the statistical R2 that describes the goodness of fit of the linear regression equation to the points. In this example, you add a calculated member named "Good Fit" to the Sales cube of the FoodMart sample. To add the calculated member, use these steps:

• Add a new calculated measure with the following properties:
  • Parent Dimension = Measures
  • Member Name = Good Fit
  • Value = LinRegR2(LastPeriods(10), [Measures].[Unit Sales],[Measures].[Store Sales])
  • Format String = #,#.00

LinRegVariance

The LinRegVariance function returns the statistical variance that describes the fit of the linear equation to the points. In this example, you add a calculated member named "Variance" to the Sales cube of the FoodMart sample. To add the calculated member, use these steps:

• Add a new calculated measure with the following properties:
  • Parent Dimension = Measures
  • Member Name = Variance
  • Value = LinRegVariance(LastPeriods(10),[Measures].[Unit Sales],[Measures].[Store Sales])