Deming Linear Regression with DAX

[Greg_Deckler]

Building upon my previous post "Simple Linear Regression with DAX", this implements Deming Regression in DAX.

In statistics, Deming regression, named after W. Edwards Deming, is an errors-in-variables model which tries to find the line of best fit for a two-dimensional dataset. It differs from the simple linear regression in that it accounts for errors in observations on both the x- and the y- axis. It is a special case of total least squares, which allows for any number of predictors and a more complicated error structure.

Deming regression is equivalent to the maximum likelihood estimation of an errors-in-variables model in which the errors for the two variables are assumed to be independent and normally distributed, and the ratio of their variances, denoted δ, is known.[1] In practice, this ratio might be estimated from related data-sources; however the regression procedure takes no account for possible errors in estimating this ratio.

The Deming regression is only slightly more difficult to compute compared to the simple linear regression. Many software packages used in clinical chemistry, such as Analyse-it, EP Evaluator, GraphPad Prism, MedCalc, NCSS (statistical software), R, S-PLUS and StatsDirect offer Deming regression.

The model was originally introduced by Adcock (1878) who considered the case δ = 1, and then more generally by Kummell (1879) with arbitrary δ. However their ideas remained largely unnoticed for more than 50 years, until they were revived by Koopmans (1937) and later propagated even more by Deming (1943). The latter book became so popular in clinical chemistry and related fields that the method was even dubbed Deming regression in those fields.

Probably can be improved but here is how I did it:

CSV File:

regression.csv

X,Y
60,3.1
61,3.6
62,3.8
63,4
65,4.1

In regression table, create the following measure:

Count = COUNTAX(ALL('regression'),[X])

In regression table, create the following columns:

DemingXMeanColumn = 1/[Count]*SUMX(ALL('regression'),[X])

DemingYMeanColumn = 1/[Count]*SUMX(ALL('regression'),[Y])

DemingXDiff = [X]-[DemingXMeanColumn]

DemingYDiff = [Y]-[DemingYMeanColumn]

DemingXDiff*DemingYDiff = [DemingXDiff]*[DemingYDiff]

DemingXDiffSquared = POWER([DemingXDiff],2)

DemingYDiffSquared = POWER([DemingYDiff],2)

In regression table, create the following measures:

DemingXMean = 1/[Count]*SUMX(ALL('regression'),[X])

DemingYMean = 1/[Count]*SUMX(ALL('regression'),[Y])

DemingXDiffSum = SUMX(ALL('regression'),[DemingXDiffSquared])

DemingYDiffSum = SUMX(ALL('regression'),[DemingYDiffSquared])

DemingXDiff*DemingYDiffSum = SUMX(ALL('regression'),[DemingXDiff*DemingYDiff])

DemingSXX = 1/([Count]-1)*[DemingXDiffSum]

DemingSXY = 1/([Count]-1)*[DemingXDiff*DemingYDiffSum]

DemingSYY = 1/([Count]-1)*[DemingYDiffSum]

DemingD = 1

DemingB1 = ([DemingSYY] - [DemingD]*[DemingSXX] + POWER(CALCULATE(POWER(CALCULATE([DemingSYY]-[DemingD]*[DemingSXX]),2) + 4*[DemingD]*[DemingSXY]*[DemingSXY]),.5)) / (2*[DemingSXY])

DemingB0 = [DemingYMean] - [DemingB1]*[DemingXMean]

In regression table, create the following columns:

DemingX* = [X] + ([DemingB1] / (POWER([DemingB1],2) + [DemingD])) * ([Y] - [DemingB0] - [DemingB1]*[X])

DemingEstimate = [DemingB0] + [DemingB1]*[DemingX*]

You can now plot your original values and the linear regression estimation values. You may create a table and add the measures "DemingB0" and "DemingB1" in order to see the values that form the equation of the line such that for estimation of new values:

y = [DemingB0] + [DemingB1]*x

Since [DemingD] is set to 1 in this example (the case of equal error variances), this technically becomes orthogonal regression. Orthonganal regression minimizes the sum of squared perpendicular distances from the data points to the regression line. 

[AymerB]

Possible to do Semented Regression?  How do you find the change point?

[ALeef]

Nice write up!  Maybe MS will implement some of these statistical models without having to do all the calcs using helper columns!